Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi guys, I am trying to understand Eloquent to use it as easily as SQL native, but I find it confusing. This query with SQL RAW works, but how would it be in Eloquent version? It returns a logged user's rating (if any) and the average of other users' ratings.
SELECT books.*,
GROUP_CONCAT(DISTINCT(bu_logged.rating) SEPARATOR ", ") AS logged_rating,
ROUND(AVG(bu_all.rating),2) AS avg_rating
FROM books
LEFT JOIN book_user bu_all ON books.id = bu_all.book_id
LEFT JOIN book_user bu_logged ON books.id = bu_logged.book_id AND bu_logged.user_id = 2 #example id
GROUP BY books.id;
The relationship between books and users is like this:
book.php:
public function users(){
return $this->belongsToMany(User::class, 'book_user')->withPivot('owner','read_status','rating');
}
user.php:
public function books(){
return $this->belongsToMany(Book::class, 'book_user');
}
The pivot table is 'book_user', with the 'rating' extra field.
Could someone please help me?
The query Builder chapter gives examples of raw Expressions also. And relations are different from a join.
Another recent conversion is here. https://laracasts.com/discuss/channels/eloquent/convert-mysql-query-into-eloquent
But remember an eloquent query and eloquent relations aren't the same.
But following the chapters in query Builder and the various chapters in eloquent, this conversion is easy to work out for yourself. Yes it will take a little trial and error.
I've done it this way, and it works. What I don't know is if it's the best, and what's the difference between doing it with SQL RAW and Eloquent, in terms of efficiency?
$books = Book::select('books.*', DB::raw('ROUND(AVG(bu_all.rating)) AS average'), DB::raw('GROUP_CONCAT(DISTINCT(bu_logged.rating) SEPARATOR ", ") AS logged_rating'))
->leftJoin('book_user AS bu_all', 'books.id', '=', 'bu_all.book_id')
->leftJoin('book_user AS bu_logged', function ($join) {
$join->on('books.id', '=', 'bu_logged.book_id')
->where('bu_logged.user_id', '=', auth()->id());
})
->groupBy('books.id')
->get();
what's the difference between doing it with SQL RAW and Eloquent, in terms of efficiency?
Eloquent (active record) converts to normal sql / pdo at runtime. So eloquent may take a couple of milliseconds longer during conversion.
Your query itself will produce a wrong result. Especially DISTINCT(bu_logged.rating). It will behave wrong if more than one logged-in user provides the same rating.
Please or to participate in this conversation.