# Find objects related

**URL:** <https://www.ruby-forum.com/t/find-objects-related/138187>\
**Category:** Rails\
**Created:** [May 20, 2008, 10:42pm UTC](https://www.ruby-forum.com/t/find-objects-related/138187 "2008-05-20T22:42:54Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![julien51](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/91b2a8/32.png) [@julien51](https://www.ruby-forum.com/u/julien51)\
**Post date:** [May 20, 2008, 10:42pm UTC](https://www.ruby-forum.com/t/find-objects-related/138187/1 "2008-05-20T22:42:54Z")

</div>

Hi everybody,

I am working on an app and I need the following behavior :

A :has\_many B  
A :has\_many C :though B

and  
C :has\_many B  
C has\_many A :through B

My goal is to find all the A objects that are related to a given ‘a’  
object, based on the fact that they have some Cs in common;

Here is how I’d do it :

related = []  
a.cs.each do |c|  
related \<\< c.as  
end  
related.uniq!

I am pretty sure this is not the good solutions : it makes a lot of  
request, and there is just now way to “Order” the related objects by the  
number of cs they have in common with a!

Anyone has a better idea on how to do it?

Thanks!

---

<div class="post-metadata">

**Author:** ![julien51](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/91b2a8/32.png) [@julien51](https://www.ruby-forum.com/u/julien51)\
**Post date:** [May 20, 2008, 11:34pm UTC](https://www.ruby-forum.com/t/find-objects-related/138187/2 "2008-05-20T23:34:13Z")

</div>

On 20 May 2008, at 21:42, Julien Genestoux wrote:

> C has\_many A :through B
> 
> My goal is to find all the A objects that are related to a given ‘a’  
> object, based on the fact that they have some Cs in common;

Well the sql is probably along the lines of

Foo.find\_by\_sql( \<\<\_SQL)  
select related\_foos.\* from foos  
inner join bs on bs.foo\_id = foos.id  
inner join cs on bs.c\_id = cs.id  
inner join bs as second\_bs on second\_bs.c\_id = cs.id  
inner\_join foos as related\_foos on related\_foos.id = second\_bs.foo\_id  
WHERE foos.id = 123  
\_SQL

(i’ve swapped A for Foo)

You could probably also do it via

A.find :all, :joins =\> {:b =\> {:c =\> {:b =\> :a}}}  
but you’d have to remember how activerecord generates table aliases in  
order to write your select clause.

Fred

---

<div class="post-metadata">

**Author:** ![julien51](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/91b2a8/32.png) [@julien51](https://www.ruby-forum.com/u/julien51)\
**Post date:** [May 21, 2008, 12:40am UTC](https://www.ruby-forum.com/t/find-objects-related/138187/3 "2008-05-21T00:40:21Z")

</div>

Thankx Frederick!

That is excactly what I needed… However, I’ve got 2 questions :  
performance: good, bad, vey bad?  
And also, is there any way to “order” the list of related by number of  
common Cs?

Thanks again, that’s helpful anyway!

Frederick C. wrote:

> On 20 May 2008, at 21:42, Julien Genestoux wrote:
> 
> > C has\_many A :through B
> > 
> > My goal is to find all the A objects that are related to a given ‘a’  
> > object, based on the fact that they have some Cs in common;
> 
> Well the sql is probably along the lines of
> 
> Foo.find\_by\_sql( \<\<\_SQL)  
> select related\_foos.\* from foos  
> inner join bs on bs.foo\_id = foos.id  
> inner join cs on bs.c\_id = cs.id  
> inner join bs as second\_bs on second\_bs.c\_id = cs.id  
> inner\_join foos as related\_foos on related\_foos.id = second\_bs.foo\_id  
> WHERE foos.id = 123  
> \_SQL
> 
> (i’ve swapped A for Foo)
> 
> You could probably also do it via
> 
> A.find :all, :joins =\> {:b =\> {:c =\> {:b =\> :a}}}  
> but you’d have to remember how activerecord generates table aliases in  
> order to write your select clause.
> 
> Fred

---

<div class="post-metadata">

**Author:** ![julien51](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/91b2a8/32.png) [@julien51](https://www.ruby-forum.com/u/julien51)\
**Post date:** [May 21, 2008, 1:08am UTC](https://www.ruby-forum.com/t/find-objects-related/138187/4 "2008-05-21T01:08:04Z")

</div>

Thanks again Frederick!!

One last thing :

A.find :all, :joins =\> {:b =\> {:c =\> {:b =\> :a}}} gives all the element  
that are related to any other element. Is there any way to have the same  
thing with a specific element, without using the MySQL?

Thanks!

Frederick C. wrote:

> On 20 May 2008, at 23:40, Julien Genestoux wrote:
> 
> > Thankx Frederick!
> > 
> > That is excactly what I needed… However, I’ve got 2 questions :  
> > performance: good, bad, vey bad?
> 
> 4 joins like that is probably not going to be super great but it’s not  
> going to be the end of the world. Benchmark it and find out
> 
> > And also, is there any way to “order” the list of related by number of  
> > common Cs?
> 
> nothing that I can think of off the top of my head.

---

<div class="post-metadata">

**Author:** ![julien51](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/91b2a8/32.png) [@julien51](https://www.ruby-forum.com/u/julien51)\
**Post date:** [May 21, 2008, 12:44am UTC](https://www.ruby-forum.com/t/find-objects-related/138187/5 "2008-05-21T00:44:01Z")

</div>

On 20 May 2008, at 23:40, Julien Genestoux wrote:

> Thankx Frederick!
> 
> That is excactly what I needed… However, I’ve got 2 questions :  
> performance: good, bad, vey bad?

4 joins like that is probably not going to be super great but it’s not  
going to be the end of the world. Benchmark it and find out

> And also, is there any way to “order” the list of related by number of  
> common Cs?

nothing that I can think of off the top of my head.

---

<div class="post-metadata">

**Author:** ![julien51](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/91b2a8/32.png) [@julien51](https://www.ruby-forum.com/u/julien51)\
**Post date:** [May 21, 2008, 1:12am UTC](https://www.ruby-forum.com/t/find-objects-related/138187/6 "2008-05-21T01:12:08Z")

</div>

On 21 May 2008, at 00:08, Julien Genestoux wrote:

> 

You just need to stick the appropriate conditions in (but to do that  
properly you need to remember how activerecord aliases tables (so that  
you can disambiguate between the first and the second join of a) and I  
can’t remember the rules off the top of my head.

Fred