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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.