# Union function in model class

**URL:** <https://www.ruby-forum.com/t/union-function-in-model-class/192768>\
**Category:** Rails\
**Created:** [August 5, 2010, 8:59am UTC](https://www.ruby-forum.com/t/union-function-in-model-class/192768 "2010-08-05T08:59:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![senthilkumar](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/48db29/32.png) [@senthilkumar](https://www.ruby-forum.com/u/senthilkumar)\
**Post date:** [August 5, 2010, 8:59am UTC](https://www.ruby-forum.com/t/union-function-in-model-class/192768/1 "2010-08-05T08:59:44Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![senthilkumar](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/48db29/32.png) [@senthilkumar](https://www.ruby-forum.com/u/senthilkumar)\
**Post date:** [August 5, 2010, 9:22am UTC](https://www.ruby-forum.com/t/union-function-in-model-class/192768/2 "2010-08-05T09:22:35Z")

</div>

On 5 August 2010 07:58, senthilkumar [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![senthilkumar](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/48db29/32.png) [@senthilkumar](https://www.ruby-forum.com/u/senthilkumar)\
**Post date:** [August 5, 2010, 10:36am UTC](https://www.ruby-forum.com/t/union-function-in-model-class/192768/3 "2010-08-05T10:36:22Z")

</div>

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)” } }