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)"
}
}
-------------------------
On Aug 5, 12:21 pm, Colin Law
<clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:> On 5 August 2010 07:58, senthilkumar
<senthil.s...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
>
> > }
> > }
>
> > Please help!
>
> > --
> > You received this message because you are subscribed to the Google
Groups "Ruby on Rails: Talk" group.
> > To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> > To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> > For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.