CODEWITHSUNDEEP
phpsqllaravel-4
Sven van den Boogaart
Sven van den Boogaart

Reputation: 12327

Laravel get most popular product

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?

Upvotes: 2

Views: 6570

Answers (2)

Needpoule
Needpoule

Reputation: 4576

You should be able to write the query like this: This is an adaptation from Caveman42 Sql query.

favourit::select('product_id', DB::raw('COUNT(product_id) as count'))
->groupBy('product_id')
->orderBy('count', 'desc')
->take(10);

Then, i'm not sure how to get the relation with your products.

Edit: If your relation is set in your product model. You should be able to join the product table using the with() function:

favourit::with('product')
->select('product_id', DB::raw('COUNT(product_id) as count'))
->groupBy('product_id')
->orderBy('count', 'desc')
->take(10);

Upvotes: 3

Caveman42
Caveman42

Reputation: 709

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 :(

Upvotes: 0

Related Questions