Level 75
Look at the aggregate functions.
Feb 10, 2023
7
Level 1
How can i get best sellers query in laravel
I have Books table
and Orders table
order table have book_id, payment_id
i want to get best seller in books
How can i do query with best performance ?
It should give me array of books sorted with best seller
Book Model
public function orders(){
return $this->hasMany(Order::class);
}
Order Model
public function books(){
return $this->belongsTo(Book::class,'book_id');
}
public function payment(){
return $this->belongsTo(Payment::class,'payment_id');
}
Level 41
I assume you already have a one to many relationship on the Book model:
class Book extends Model
{
public function orders()
{
return $this->hasMany(Order::class);
}
}
The fastest approach to me is eagerload the aggregations and order by the aggregated value:
// Showing the top 10 books.
return Book::withCount('orders')
->orderByDesc('books_count')
->limit(10);
Alternatively you might want to join the two tables together:
Book::selectRaw('books.*, COUNT(orders.id) as orders_count')
->join('orders', 'books.id', '=', 'orders.book_id')
->groupBy('books.id')
->orderBy('orders_count')
->limit(10);
I believe that latter is faster, you might use tools to measure query performances.
Level 1
@kevinbui Hey, Thank u for reply.
I had this error when i try to use it
SQLSTATE[42000]: Syntax error or access violation: 1055 'bookstore.books.name' isn't in GROUP BY
Level 73
In SQL I would do something like this.
SELECT b.id, b.title, COUNT(o.*) AS order_count
FROM orders o
JOIN books b
ON o.book_id = b.id
GROUP BY b.id, b.title
ORDER BY COUNT(0.*) DESC
LIMIT 10;
It's similar to the raw query that @kevinbui showed. the only thing he didn't add was the ordering.
1 like
Level 41
@Tray2 Thanks, I have updated my answer.
Level 1
@Tray2, Hey, Thank u for reply.
I had this error when i try to use it
SQLSTATE[42000]: Syntax error or access violation: 1055 'bookstore.books.name' isn't in GROUP BY
Level 73
@amer22 You need to add all the columns before the count to the group by.
Please or to participate in this conversation.