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)