Translating a (slightly complex) raw SQL query in ActiveRecord / Arel?
I have a very simple Rails application with a very simple relational database. There are many examples in the category. I would just like to load 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
This works pretty well by the way, but it's not very Rails-like to use raw SQL. And I would like these categories to be model instances, so:
How can I rewrite something like this in an ActiveRecord or Arel request? Is this possible, or should I go with plain SQL? Maybe there is an easier way to do this?
source to share
A possible good 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 example, an example model is available as follows:
belongs_to :category , :counter_cache => true
Now you can use the count as a condition, for example:
Category.where(:samples_count => 7)
source to share