Find objects related

Hi everybody,

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

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!

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;

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

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 Cheung 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.

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 Cheung wrote:

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?

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