Order records based on number of children

Rails
1

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

2

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

3

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

4

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

5

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

6

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