Laravel是最受欢迎的产品

Question

Im having 2 tables one with products and one favourit to keep it simple i show the relevant rows.

table poducts

id

---

table favourit

user_id not needed in query

product_id

---

What i want is find the top 10 of products wich are most common in the favourit table. I think i have to do

   favourit::groupBy(product_id) 

and

   favourit::orderBy(ammount(sum of all product_ids), desc)

But how can i get the ammount(sum of all product_ids) to order by? Also would this query (with a groupby) be possible to be joined with the products table so i get the products in one query?

Answer 1

From an SQL point of view, this is how you would get the top 10 products from the favorit table:

Select TOP 10
    Product_id
    ,COUNT(Product_id) as [Count]
From
    favorit
Group By
    Product_id
Order By
    COUNT(Product_id) desc

This will give you the top 10 Product_id's and sort them with the highest count on top.

I am not too familiar with PHP so I wouldn't be able to tell you how to implement it, sorry :(