Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have this query:
SELECT authors.name, COUNT(books.id) books_count
FROM books
LEFT JOIN authors on authors.id = books.author_id
GROUP BY author.id
How can I transform this into Eloquent models?
I tried this:
class Book extends Model {
public function author() {
return $this->belongsTo('Author');
}
}
Book::all()->with('author');
It worked but the group by is the problem..
Thanks.
I think what you want is something like:
$author = Author::withCount('books')->get();
The query won't be the same, but it'll get you what you want using Eloquent. This assumes you have the relationship books() defined on the Author model.
Well right now you use the relationships function from Laravel. It's actually doing two queries here.
// returns multiple items with an author_id column
select * from books
// The author ids will be used in the below query
select * from author where id IN (1, 2, 3); //
After that Laravel will then connect the related items and return them in one collection.
If you want to group by a relation you need to use a join query, just like your raw query
Book::select(DB::raw('authors.name', 'count(books.id) as books_count'))
->join('authors', 'authors.id', '=', 'books.author_id')
->groupBy('authors.id')
->get();
Documentation: https://laravel.com/docs/5.6/queries#joins
@devk The query above is simplified version of the actuall query so I dont really can use that. @bobbybouwmann I used this method already, I thought there are ways to use Eloquent relationships.
Well, my method is using the Eloquent relationship. Bobby's method is reproducing your query.
Can't have it both ways
If by simplified you mean that it selects everything from author, just add ->select('name') to it
You can't do a group by on a relationship with Laravel. If you want that you need to use a join ;)
@Skizo @devk is right, he gave you the what u need author name and count of books.
The query above is simplified version of the actuall query so I dont really can use that.
i thought every one need simplified query for run their website fast.
I used this method already, I thought there are ways to use Eloquent relationships.
If u want eloquent way use Author as main model and call book relationship like @devk said.
Please or to participate in this conversation.