Let''s say I have simple schema with two tables. The models are defined
like the folllowing:
class Parent < ActiveRecord::Base
has_many :children
end
class Child < ActiveRecord::Base
belongs_to :parent
end
Simple has_many relationship. Is there any way to order the results of
a Parent.find_all by the number of children the parent has? I can
sort with
sorted_parents = Parent.find_all.sort {|a,b| b.children.length
<=> a.children.length}
but that''s extremely slow since it needs to iterate over every Parent
and select all of the children for each parent. Is there a faster,
more "Rails" method of getting the same result?
--
Ryan
Skipped content of type multipart/mixed-------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 250 bytes Desc: OpenPGP digital signature Url : http://wrath.rubyonrails.org/pipermail/rails/attachments/20060321/c66f849a/signature.bin
Unfortunately, sqlite3 is developement database and it doesn''t allow
that syntax:
SQLite3::SQLException: misuse of aggregate function COUNT(): SELECT
DISTINCT parents.id FROM parents LEFT OUTER JOIN children ON
children.parent_id = parent.id ORDER BY COUNT(children.parent_id) ASC
Here''s the find:
ranked_by_children = Parents.find(:all,
:order => ''COUNT(children.parent_id)
ASC'',
:include => "children")
It''d be brilliant if that worked. My next step was to customize the
select like so
ranked_by_children = Parents.find(:all,
:select => ''*,
COUNT(children.parent_id)'',
:order => ''count ASC'',
:include => "children")
On edge rails, at least, the custom select causes two SQL calls to be
issued. The first finds the ids while the second actually gathers the
sorted data with those ids. The first call, then, has an error:
SQLite3::SQLException: no such column: count: SELECT id FROM parents
ORDER BY count ASC
Well, of course there is no count column... Rails ignored my select statement.
It looks like I''ll first have to do a find with a custom sort on the
Children model and then use those resulting parent_ids to query the
Parents model. I''m not a fan of that solution since it relies on the
second query preserving the order of the parent_ids I pass it...
That was an interesting suggestion, though. Does ordering by COUNT
work in mysql? Also, did I mess somethign up in my implementation
above?
--
Ryan
On 3/21/06, Mats Lindblad <mats.lindblad@it.su.se>
wrote:> Ryan Nielsen wrote:
> > Let''s say I have simple schema with two tables. The models
are defined
> > like the folllowing:
> >
> > class Parent < ActiveRecord::Base
> > has_many :children
> > end
> >
> > class Child < ActiveRecord::Base
> > belongs_to :parent
> > end
> >
> > Simple has_many relationship. Is there any way to order the results of
> > a Parent.find_all by the number of children the parent has? I can
> > sort with
> >
> > sorted_parents = Parent.find_all.sort {|a,b| b.children.length
> > <=> a.children.length}
> >
> > but that''s extremely slow since it needs to iterate over
every Parent
> > and select all of the children for each parent. Is there a faster,
> > more "Rails" method of getting the same result?
> >
> > --
> > Ryan
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> I''m not sure how the Schema is supposed to look like but
i''m think you
> have a foreign key in your child table ...
>
> If that is so you can do "ORDER BY COUNT(children.parent_id)"
> And I though that find_all() was deprecated? Shouldn''t we use
find(:all,
> :order => ''COUNT(children.parent_id)'') instead?
>
>
> --
> ======================================> | MVH Mats Lindblad
|
> | Systemutvecklare / Webbutvecklare |
> | +46-8-6747466 |
> | Enheten f?r IT och media |
> | <http://www.it.su.se/> |
> | <http://www.su.se/> |
> | Stockholms universitet |
> | S-106 91 STOCKHOLM |
> ======================================>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>
>
Ryan Nielsen wrote:> Let''s say I have simple schema with two tables. The models are defined > like the folllowing: > > class Parent < ActiveRecord::Base > has_many :children > end > > class Child < ActiveRecord::Base > belongs_to :parent > end > > Simple has_many relationship. Is there any way to order the results of > a Parent.find_all by the number of children the parent has? I can > sort with > > sorted_parents = Parent.find_all.sort {|a,b| b.children.length > <=> a.children.length} > > but that''s extremely slow since it needs to iterate over every Parent > and select all of the children for each parent. Is there a faster, > more "Rails" method of getting the same result? > > -- > RyanThere are SQL ways: select * from parents left outer join (select parent_id, count(*) as child_count from children group by parent_id) as tmp_count on (id = parent_id) order by coalesce(child_count,0) desc; OR select *, (select count(*) from children where parent_id = parents.id ) as child_count from parents order by child_count desc; Works on PostgreSQL -- Posted via http://www.ruby-forum.com/.
Skipped content of type multipart/mixed-------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 250 bytes Desc: OpenPGP digital signature Url : http://wrath.rubyonrails.org/pipermail/rails/attachments/20060321/416fdcf3/signature.bin
You are correct, Mats... stupid typo in email. I eventually settled on a find_by_sql solution along the lines of dseverin''s suggestion, though, since that seemed to be the easiest and fastest way to accomplish my goal. Thanks for the tips! -- Ryan On 3/21/06, Mats Lindblad <mats.lindblad@it.su.se> wrote:> Ryan Nielsen wrote: > > Unfortunately, sqlite3 is developement database and it doesn''t allow > > that syntax: > > SQLite3::SQLException: misuse of aggregate function COUNT(): SELECT > > DISTINCT parents.id FROM parents LEFT OUTER JOIN children ON > > children.parent_id = parent.id ORDER BY COUNT(children.parent_id) ASC > > > > Here''s the find: > > > > ranked_by_children = Parents.find(:all, > > :order => ''COUNT(children.parent_id) ASC'', > > :include => "children") > > > > It''d be brilliant if that worked. My next step was to customize the > > select like so > > > > ranked_by_children = Parents.find(:all, > > :select => ''*, COUNT(children.parent_id)'', > > :order => ''count ASC'', > > :include => "children") > > > > On edge rails, at least, the custom select causes two SQL calls to be > > issued. The first finds the ids while the second actually gathers the > > sorted data with those ids. The first call, then, has an error: > > > > SQLite3::SQLException: no such column: count: SELECT id FROM parents > > ORDER BY count ASC > > > > Well, of course there is no count column... Rails ignored my select statement. > > > > It looks like I''ll first have to do a find with a custom sort on the > > Children model and then use those resulting parent_ids to query the > > Parents model. I''m not a fan of that solution since it relies on the > > second query preserving the order of the parent_ids I pass it... > > > > That was an interesting suggestion, though. Does ordering by COUNT > > work in mysql? Also, did I mess somethign up in my implementation > > above? > > > > -- > > Ryan > > > > On 3/21/06, Mats Lindblad <mats.lindblad@it.su.se> wrote: > >> Ryan Nielsen wrote: > >>> Let''s say I have simple schema with two tables. The models are defined > >>> like the folllowing: > >>> > >>> class Parent < ActiveRecord::Base > >>> has_many :children > >>> end > >>> > >>> class Child < ActiveRecord::Base > >>> belongs_to :parent > >>> end > >>> > >>> Simple has_many relationship. Is there any way to order the results of > >>> a Parent.find_all by the number of children the parent has? I can > >>> sort with > >>> > >>> sorted_parents = Parent.find_all.sort {|a,b| b.children.length > >>> <=> a.children.length} > >>> > >>> but that''s extremely slow since it needs to iterate over every Parent > >>> and select all of the children for each parent. Is there a faster, > >>> more "Rails" method of getting the same result? > >>> > >>> -- > >>> Ryan > >> I''m not sure how the Schema is supposed to look like but i''m think you > >> have a foreign key in your child table ... > >> > >> If that is so you can do "ORDER BY COUNT(children.parent_id)" > >> And I though that find_all() was deprecated? Shouldn''t we use find(:all, > >> :order => ''COUNT(children.parent_id)'') instead? > > I haven''t tried it in mysql for this example but I have used it before > and it should work. > > Shouldn''t :select => ''*, COUNT(children.parent_id)'' be :select => ''*, > COUNT(children.parent_id) as count'' ? > > > /Mats > > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > > >