CODEWITHSUNDEEP
laraveljoineloquentrelationship
trinvh
trinvh

Reputation: 1580

Laravel eloquent get the latest row of related table

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!

Upvotes: 12

Views: 15018

Answers (3)

patricus
patricus

Reputation: 62228

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.

Your solution will depend on your Laravel version.

Laravel >= 8.42.0

Laravel 8x (8.42.0, specifically) added a new relationship method to address this issue: latestOfMany().

You will need to define a new relationship specifically for the latest chapter, and use this new relationship method to get the one record you're looking for:

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

Laravel < 8.42.0

For earlier versions of Laravel, the solution is similar, but it isn't as efficient.

Your new relationship will look like this:

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

The reason this isn't as efficient is that this will still fetch and load all the related chapters, it just only returns one of them.

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();

Upvotes: 31

İlter Kağan &#214;cal
İlter Kağan &#214;cal

Reputation: 3690

For those who are not able to upgrade laravel 8.4 to have ->latestOfMany method, hasOne relation can be enhanced with a self join to filter out latest related model per each main model

public function lastchapter()
{
    return $this->hasOne(Chapter::class)->join(DB::raw("(
           SELECT MAX(id) as id, book_id
           FROM `books` as `books_sub`
           GROUP BY book_id
        ) as lastchapters"), function ($join) {
        $join->on("lastchapters.id", '=', "books.id");
    })->whereRaw('lastchapters.id = books.id');
}

Upvotes: 1

Mirel Popescu
Mirel Popescu

Reputation: 121

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();
}

Upvotes: 11

Related Questions