Union function in model class

Hi all

Help Please!

Question : i have to union two tables named

qareports (1st table)

Hi all

Help Please!

Question : i have to union two tables named

qareports (1st table)
--------------
id
agency_id

agencies (2nd table)
-------------
id
name

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

#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|

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

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)