Rails - Jun 2011 - 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

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.

On Sun, Jun 19, 2011 at 21:38, John Feminella
<johnf-u89qwezJ71hz+5FpPkU+UQ@public.gmane.org>
wrote:
> 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.