Forum: Ruby on Rails Order records based on number of children

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ryan N. (Guest)
on 2006-03-21 09:31
(Received via mailing list)
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
Mats L. (Guest)
on 2006-03-21 10:15
(Received via mailing list)
_______________________________________________
Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails
Ryan N. (Guest)
on 2006-03-21 11:22
(Received via mailing list)
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
dseverin (Guest)
on 2006-03-21 12:09
Ryan N. 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

There 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
Mats L. (Guest)
on 2006-03-21 14:18
(Received via mailing list)
_______________________________________________
Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails
Ryan N. (Guest)
on 2006-03-21 21:09
(Received via mailing list)
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
This topic is locked and can not be replied to.