1

I need to get a list of '10 most popular' products. I think that the best way to do this is to use Orders table (model) that has a reference to Products.

I know what I should do but I don't know how to write it. This is how I would do this in a MySQL: 

SELECT  products.* 
FROM (SELECT product_id, count(product_id) as count from Orders 
           GROUP BY product_id
           ORDER BY count DESC
           LIMIT 10) AS O
LEFT OUTER JOIN products
ON Products.id  = O.product_id

How I can write the query in Rails?

For example: Order.group(:product_id).count...

Holger Just
  • 52,918
  • 14
  • 115
  • 123
KazKazar
  • 121
  • 1
  • 4
  • 15
  • 1
    query looks fine, if you change `left outer join` to `inner join`, it should just give you 10 products that meet the criteria. is that what you looking for? – lusketeer May 23 '16 at 11:51
  • @lusketeer I need it written in Rails... like: `Order.group(:product_id).count...` – KazKazar May 23 '16 at 12:02
  • Try `Products.joins(:orders).select("orders.product_id, count(orders.product_id) as count").order("count desc").group("orders.product_id")` – Amit Badheka May 23 '16 at 12:16
  • 1
    Check out http://stackoverflow.com/questions/16996618/rails-order-by-results-count-of-has-many-association – mgrim May 23 '16 at 12:17
  • @ Amit Badheka Pykih Staff that's what I got: `=> #, #, #, #, #]>`. The query that run: `SELECT orders.product_id, count(orders.product_id) as count FROM `products` INNER JOIN `orders` ON `orders`.`product_id` = `products`.`id` GROUP BY orders.product_id ORDER BY count desc` – KazKazar May 23 '16 at 12:36

1 Answers1

1

try 

# One single query with join (extract the subquery and 
# assign it to an alias t using active_record function .from)
Product.joins("INNER JOIN t ON t.product_id = products.id")
       .from(
         Order
            .select("orders.product_id, COUNT(orders.id) as count")
            .group("orders.product_id").order("count DESC").limit(10), 
       :t)

# Alternative, but I think it will use 2 queries, 
# and the first one is probably faster
Product
      .where(id: 
      Order
         .select("orders.product_id, COUNT(orders.id) as count")
         .group("orders.product_id").order("count DESC").limit(10).pluck(:product_id))

Update:

that code worked for me (@KazKazar):

Product.joins("INNER JOIN products ON products.id = O.product_id") .from(Order.select("product_id, COUNT(product_id) as count") .group("product_id").order("count DESC").limit(10),:O)

KazKazar
  • 121
  • 1
  • 4
  • 15
lusketeer
  • 1,890
  • 1
  • 12
  • 29