In my application, a Store belongs to a Business, a Business has many Stores, and each Business has many Categories through Categorizations.
I would like to return a list of Categories mapped to the number of Stores in that category. For example, suppose that we have:
business 1: in categories [A, B]: 3 active locations, 2 inactive locations business 2: in categories [B, C]: 4 active locations, 5 inactive locations
Then I'd like to see:
category A => 3 (3 from business #1) category B => 7 (3 from business #1, 4 from business #2) category C => 4 (4 from business #2)
If a category doesn't have any Stores in it, then it shouldn't appear.
The Postgres SQL query that I've assembled to get this now looks like:
SELECT COUNT(stores.id) AS stores_count, categories.id, categories.name, categories.url FROM categories LEFT JOIN categorizations ON categorizations.category_id = categories.id LEFT JOIN businesses ON businesses.id = categorizations.business_id AND businesses.active = :active LEFT JOIN stores ON stores.business_id = businesses.id AND stores.active = :active
GROUP BY categories.id, categories.name, categories.url
I then call Category.find_by_sql([query, :active => true]). (There is a #stores_count method on Category that accepts the result of the count on the first line of the query.)
Is this something that's better suited for raw SQL, or is it possible to make this a little nicer through ARel?
~ jf