# Using Model.find for inner joins and to 'dedupe' a result se

**URL:** <https://www.ruby-forum.com/t/using-model-find-for-inner-joins-and-to-dedupe-a-result-se/106449>\
**Category:** Rails\
**Created:** [June 10, 2007, 6:43pm UTC](https://www.ruby-forum.com/t/using-model-find-for-inner-joins-and-to-dedupe-a-result-se/106449 "2007-06-10T18:43:40Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [June 10, 2007, 6:43pm UTC](https://www.ruby-forum.com/t/using-model-find-for-inner-joins-and-to-dedupe-a-result-se/106449/1 "2007-06-10T18:43:40Z")

</div>

Hi,

I’m using rails 1.2.3 and mysql 5

I have a collection of models like this:

Car has\_many Passengers;  
Passenger has\_many Pocket\_Items

_**I want to issue a Car.find command from rails where I find all cars  
where at least 1 passenger is carrying a knife (or whatever) in his/  
her pocket.**_

The details are shown below:

Table 1: cars (id, license\_plate, make, model)  
Table 2: passengers (id, car\_id, first\_name, last\_name)  
Table 3: pocket\_items (id, passenger\_id, item\_description)

The description of the Car model would be something like this:

class Car \< ActiveRecord::Base  
has\_many :passengers  
has\_many :pocket\_items, :through =\> :passengers  
end

so, e.g. car # 1 may contain 2 passengers. passenger 1 may have a  
wallet and a knife, whereas passenger 2 may have a knife and keys.

So, now let’s say that I want to find all cars where at least 1  
passenger is carrying a knife.

in SQL, I might issue the following statement

select  
c.id,  
c.license\_plate,  
c.make,  
c.model  
from cars c  
inner join passengers p on (p.car\_id = c.id)  
inner join pocket\_items pi on (pi.passenger\_id = p.id)  
where pi.item\_description = ‘Knife’

this works, except that if a car contains 2 people, each carrying a  
knife, the same car will be returned on 2 separate rows.

obviously, this can be eliminated by changing the statement above to  
“select distinct …”

My questions are:

1. Is there another (more elegant) way of demanding an INNER JOIN from  
rails aside from issuing it directly from the :joins option within the  
find method?

2. Is there a rails idiom for ‘deduping’ my result set (i.e. I only  
want a result set of UNIQUE cars from my find query. From the agile  
development book, ed 2 (page 341), one can ‘dedupe’ _child_ objects  
using the modifier :uniq =\> true. However, i’m not sure if there’s a  
way to do this on the _parent_ object.

3. are there any (more efficient) approaches to using rails to  
accomplish the query outlined above? i.e. I want to find all cars  
where at least 1 passenger carries a knife.

thanks!

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [June 21, 2007, 3:03pm UTC](https://www.ruby-forum.com/t/using-model-find-for-inner-joins-and-to-dedupe-a-result-se/106449/2 "2007-06-21T15:03:16Z")

</div>

As for “deduping” your result set, have a look here:

> **[ActiveRecord::Base](https://api.rubyonrails.org/classes/ActiveRecord/Base.html#M000992)**
>
> Active Record Active Record objects don't specify their attributes directly, but rather infer them from the table definition with which they're linked.

You can pass the :select flag to find with something like this →   
“:select =\> ‘disctinct cars.\*’”