CODEWITHSUNDEEP
mysqlsqllaraveleloquent
Siavash Golabi
Siavash Golabi

Reputation: 43

how to write a join query with group by in eloquent laravel?

I have three tables in my database.

user: stores users information.

book: stores each book information

book_reviews: stores review information (such as overall point) for each book by each user.

I want to write a query in Laravel to retrive books with highest average overall point given by users.

How do I write the following SQL query in Laravel using Eloquent?

SELECT books.*, AVG(book_reviews.overall_point) AS avg_point
FROM books JOIN
     book_reviews
     ON books.id = book_reviews.book_id
GROUP BY book.id
ORDER BY avg_point DESC

Upvotes: 2

Views: 8857

Answers (1)

Teoman Tıngır
Teoman Tıngır

Reputation: 2866

I think you can achieve with orderByRaw() method. With this method you can order books with average overall point

Book::select("books.*")
    ->join("book_reviews br","br.book_id","=","books.id")
    ->orderByRaw("AVG(br.overall_point) desc")
    ->groupBy("books.id")
    ->take(10) # or how many book you want
    ->get();

Upvotes: 3

Related Questions