Laravel eloquent get the latest row of related table

Question

I have two tables, books, and chapters. One book has many chapters.

Book model:

public function chapters() {
   return $this->hasMany(Chapter::class);
}

Chapter model:

public function book() {
   return $this->belongsTo(Book::class);
}

I want to get book list with their own latest chapter using single query like this:

$books = Book::with(['authors', 'categories', 'chapters' => function($q) {
   $q->orderBy('updated_at', 'desc')->first();
}]->get();

But it doesn't work. Chapters return an empty array. If I remove first() in the subquery, it works properly.

Are there any way to do this with just one query. I don't want to get all related chapters then keep one, or using multiple queries. The only way I feel better is using join, is it right?

Any help will be appreciated. Thanks!

Answer 1

Because relationships are separate queries, you can't add a limit to an eager loaded relationship. If you do, this limits the entire relationship query, it does not act as a limit per related object.

Luckily, your requirement can be implemented simply with an additional relationship:

public function latestChapter() {
    return $this->hasOne(Chapter::class)->latest();
}

Now, instead of eager loading the entire chapters relationship, you can just eager load your new latestChapter relationship.

$books = Book::with(['authors', 'categories', 'latestChapter'])->get();

Answer 2

I know it is an old post, but there are some new solutions and I want to share it with you. In laravel 8, there are new helper functions: latestOfMany , oldestOfMany and ofMany. For your example, you could use:

public function latestChapter() {
    return $this->hasOne(Chapter::class)->latestOfMany();
}