Spiffying up a group-by query through ARel?

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

Also: the query I have now still isn’t quite right since it returns
empty Categories (the LEFT JOINs need to be JOINs), which is another
reason I’m interested in taking a look at it in ARel.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs