ActiveRecord subquery in select clause

Question

So I'm getting a bunch of Volunteers records, with some filtering and sorting, which is fine. But I'd like to also get a count of the number of Children each volunteer is helping (using volunteer_id on children table), as a sub-query in the select clause to avoid having to perform a separate query for each record. As a bonus it would be good to be able to sort by this count too!

I'd like to end up with a generated query like this and be able to access the 'kids' column:

SELECT id, name, (SELECT COUNT(*) FROM children WHERE volunteer_id = volunteers.id) AS kids FROM volunteers

Is there any way of doing this with Arel? I've had a bit of a scout around and haven't found anything yet.

Alternatively, is it possible to join to the children table and get: count(children.id) ?

Thanks for any help :)

Answer 1

The proper way of doing this with SQL is with a GROUP BY clause:

SELECT v.id, v.name, COUNT(*) AS kids
FROM volunteers v
LEFT OUTER JOIN children c ON v.id = c.volunteer_id
GROUP BY v.id, v.name

There is a method .group() in AR for using GROUP BY queries.