Union function in model class

Rails
1

Hi all

Help Please!

Question : i have to union two tables named

qareports (1st table)

id
agency_id

agencies (2nd table)

id
name

#i have called model from controller by passing some parameter

reports_controller.rb

        if @submited_status.to_i != 2
          @qareports =

@qareports.submitted_reports(@submited_status)
end

qareport.rb

named_scope :unsubmitted_reports, lambda {|*args|
{
:conditions => [‘locked = 0 and agency_id = ?’, (args.first)]
# so i have to use union agency table here or some where
}
}

Please help!

2

On 5 August 2010 07:58, senthilkumar [email protected] wrote:

agencies (2nd table)

id
name

What relationships have you specified in the models (has_many,
belongs_to etc)?

qareport.rb

named_scope :unsubmitted_reports, lambda {|*args|

Why is this unsubmitted_reports but above you have submitted_reports?

{
:conditions => [‘locked = 0 and agency_id = ?’, (args.first)]

What is this ‘locked’. I do not see it in either table?

  # so i have to use union agency table here or some where

Can you explain in words what you are trying to achieve here?
Possibly something like ‘find all gareports for a particular agency
where gareport.locked is 0’.

Colin

3

Colin,

Below condition is that it takes only from qa-reports, i need to
display agency details which is not in the qa-reports,

i.e : i need to display details + agency details where agency_id which
is not in qa-reports

same like union function in submitted_reports function
:union=>“SELECT DISTINCT * FROM agencies a where a.id not in
(select agency_id from qareports)”

attributes like locked=‘0’ are just only the attributes

actual query:

SELECT qareports.id AS t0_r0, qareports.agency_id AS t0_r1,
qareports.user_id AS t0_r2, qareports.incident_total AS t0_r3,
qareports.incident_clinical AS t0_r4,
qareports.medication_error AS t0_r5,
qareports.attendance_problem AS t0_r6,
qareports.attitude_problem AS t0_r7, qareports.incident_other
AS t0_r8, qareports.facility_actions AS t0_r9,
qareports.dnr_facilities AS t0_r10, qareports.dnr_actions AS
t0_r11, qareports.formal_reports AS t0_r12,
qareports.formal_reports_details AS t0_r13,
qareports.reporting_period AS t0_r14, qareports.locked AS
t0_r15, qareports.created_at AS t0_r16, qareports.updated_at
AS t0_r17, qareports.az_contract_id AS t0_r18,
qareports.az_admin_visits AS t0_r19, agencies.id AS t1_r0,
agencies.name AS t1_r1, agencies.parent_id AS t1_r2,
agencies.address1 AS t1_r3, agencies.address2 AS t1_r4,
agencies.city AS t1_r5, agencies.state_id AS t1_r6,
agencies.zip1 AS t1_r7, agencies.zip2 AS t1_r8,
agencies.phone AS t1_r9, agencies.fax AS t1_r10,
agencies.web_site AS t1_r11, agencies.status AS t1_r12,
agencies.notes AS t1_r13, agencies.deleted AS t1_r14,
agencies.deleted_at AS t1_r15, agencies.created_at AS t1_r16,
agencies.updated_at AS t1_r17,
agencies.authorized_signator_firstname AS t1_r18,
agencies.authorized_signator_lastname AS t1_r19,
agencies.authorized_signator_title AS t1_r20,
agencies.authorized_signator_email AS t1_r21,
agencies.authorized_signator_phone AS t1_r22,
agencies.coordinator_id AS t1_r23, agencies.external_id AS
t1_r24 FROM qareports LEFT OUTER JOIN agencies ON agencies.id =
qareports.agency_id WHERE (((locked = ‘1’) AND (reporting_period >=
‘2009-08-01’ and reporting_period <= ‘2010-08-31’)) AND (agency_id=
‘96’)) ORDER BY reporting_period, agencies.name

  • i need to use this query by union function

SELECT DISTINCT * FROM agencies a where a.id not in (select agency_id
from qareports)

class Qareport < ActiveRecord::Base

belongs_to :agency
belongs_to :user

Query to get unsubmitted reports

for a given agency

First parameter is the 0-Unlocked(Unsubmitted) , 1-Locked

(Submitted)

named_scope :submitted_reports, lambda {|*args|
{
:conditions => [‘locked = ?’, (args.first || submited_status)],
:union=>“SELECT DISTINCT * FROM agencies a where a.id not in
(select agency_id from qareports)”
}
}