Ruby on rails count and group

Question

Need to build a "Top 10" query that works with SQLite and Postgres.

Client model Client has_many :merchandises, :through => :orders, :source => :items

I want to group merchandises ordered by product_id, get the count for each and sort by most product ordered top and limit to 10.

Client.last.merchandises.group(:product_id).count(:quantity)
SELECT COUNT("items"."quantity") AS count_quantity, product_id AS product_id FROM "items" INNER JOIN "orders" ON "items"."order_id" = "orders"."id" WHERE "orders"."client_id" = 2 GROUP BY product_id
=> {1=>91, 2=>1, 12=>1, 32=>1, 33=>1, 34=>1, 37=>1, 75=>1, 84=>1, 85=>1}

What's missing: sort by, limit to 10 and get product.name along with quantity count

Latest development:

Items are selected but need to show product.name

class Client < ActiveRecord::Base 
def top_ten_products
  Item.find_by_sql(
    "SELECT i.product_id, sum(i.quantity) AS sum_quantity
    FROM   orders o
    JOIN   items i ON i.order_id = o.id 
    WHERE  o.client_id = 2 
    GROUP  BY 1
    ORDER  BY 2 DESC
    LIMIT  10;"
   )
 end

Console output

=> [#<Item product_id: 1>, #<Item product_id: 37>, #<Item product_id: 75>, #<Item product_id: 12>, #<Item product_id: 32>, #<Item product_id: 33>, #<Item product_id: 2>, #<Item product_id: 34>, #<Item product_id: 84>, #<Item product_id: 85>]

Client#show

<%= @client.top_ten_products %>

Erwin Brandstetter · Accepted Answer · 2011-10-18 19:40:17Z

1

Assuming that product_id is a column of table items, the query could look like this in PostgreSQL:

SELECT i.product_id
      ,p.name
      ,sum(i.quantity) AS sum_quantity
FROM   orders o
JOIN   items i ON i.order_id = o.id 
LEFT   JOIN product p USING (product_id)
WHERE  o.client_id = 2 
GROUP  BY 1,2
ORDER  BY 3 DESC, 2 -- with same quantity, order by name
LIMIT  10;

Note:

I changed your quantity aggregation to a sum and added a commented column for count, as I suspect you mistakenly had a count where you want a sum.

Edit2:

sum() is confirmed. Included name from table product per request.
LEFT JOIN is just a precaution for missing entries in table product, if referential integrity is guaranteed, it can be a plain JOIN instead.

edited Oct 18, 2011 at 19:40

answered Oct 17, 2011 at 5:58

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gaelle Over a year ago

Thanks. Almost there. I edited my question above with the latest development

Gaelle Over a year ago

Thanks for the update however there is no name column under item. The name column is part of the product table. Please advise.

Erwin Brandstetter Over a year ago

@Gaelle: I edited in my guess. If that's not it, you need to provide the information how the table product is linked to table item.

Gaelle Over a year ago

This is where I'm not sure. For now I have Item belongs_to :product but nothing in the Product table as I'm not sure how it should be set. Please advise. Thx

Erwin Brandstetter Over a year ago

@Gaelle: do you see a column product_id in both tables? IOW, does my latest version work? Just try SELECT * FROM product LIMIT 10 and SELECT * FROM item LIMIT 10 to see and figure out how they are tied together.

|

Collectives™ on Stack Overflow

Ruby on rails count and group

1 Answer 1

Note:

Edit2:

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Note:

Edit2:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related