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