Translating a (slightly complex) raw SQL query to ActiveRecord/Arel?

Question

I have a very simple Rails app with a very simple relational database: Category has many Samples. I'd simply like to load the categories that have X number of samples.

In plain SQL I would do something like this:

SELECT
    categories.*
FROM
    categories
JOIN
    (SELECT
        category_id, COUNT(*) as sample_count
    FROM
        samples
    GROUP BY
        category_id
    ) AS subselect
ON
    categories.id=subselect.category_id
WHERE
    subselect.sample_count = X; -- where X is whatever

That works just fine, by the way, but it's not terribly Rails-like to use raw SQL. And obviously I'd like to get those categories as model instances, so:

How would I go about re-writing something like that to an ActiveRecord or Arel query? Is it even feasible, or should I go with the plain SQL? Is there perhaps an altogether simpler way of doing it?

Answer 1

A possible nice way would be to use counter_cache, as described on this page: http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

Add a column named samples_count to your Category model:

add_column :categories, :samples_count, :integer

In your Sample model update belongs_to as follows:

belongs_to :category , :counter_cache => true 

You can now use the count as a condition, for example:

Category.where(:samples_count => 7)