CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4
KazKazar
KazKazar

Reputation: 151

Rails Select Most Popular Products

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...

Upvotes: 1

Views: 1190

Answers (1)

lusketeer
lusketeer

Reputation: 1930

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)

Upvotes: 1

Related Questions