danimohamadnejad's avatar

how to get best sellers of ecommerce?

Hello everyone. please help me on this database query. I am trying to get best seller products in my ecommerce application. here is simplified version of my database: `` products id name

reviews id userId productId rating

users id name

orders id total userId

order_items id productId orderId quantity ``

and here is the eloquent procedure I am using:

$result=Product::join('reviews','reviews.productId','=','products.id')->join('order_items','order_items.productId', '=','reviews.productId')->join('orders','orders.id','=','order_items.orderId')->groupBy([ 'products.id', ])->select(DB::raw('products.*, sum(reviews.rating) as total_ratings, count(reviews.userId) as total_reviews, sum(order_items.quantity) as total_sold_count'))->orderBy('total_sold_count','desc')->take(10)->get();

which is translated into mysql query:

select products.*, sum(reviews.rating) as total_ratings, count(reviews.userId) as total_reviews, sum(order_items.quantity) as total_sold_count from `products` inner join `reviews` on `reviews`.`productId` = `products`.`id` inner join `order_items` on `order_items`.`productId` = `reviews`.`productId` inner join `orders` on `orders`.`id` = `order_items`.`orderId` group by `products`.`id` order by `total_sold_count` desc

however I know that above query is wrong. any help will be greatly appriciated. thank you in advance.

0 likes
2 replies
rodrigo.pedra's avatar

It seems you are missing the group by clauses. When using an aggregate column you should group your results.

Check this Laracasts video on SQL Aggregates:

https://laracasts.com/series/mysql-database-design/episodes/6

Try this simplified version and see if it works:

Route::get('best', function () {
    $products = Product::query()
        ->join('order_items', 'order_items.productId', '=', 'products.id')
        ->selectRaw('products.*, SUM(order_items.quantity) AS quantity_sold')
        ->groupBy(['products.id']) // should group by primary key
        ->orderByDesc('quantity_sold')
        ->take(20) // 20 best-selling products
        ->get();

    return $products;
});

Tested locally, with a slightly different DB model, and it worked. You may have to change something to get ir working with your DB structure.

Hope it helps.

3 likes

Please or to participate in this conversation.