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!

No, Eloquent should suffice. What about using `limit()` or maybe take a look [here](http://stackoverflow.com/questions/15229303/is-there-a-way-to-limit-the-result-with-eloquent-orm-of-laravel). What you need to do is limit `$q->orderBy('updated_at', 'desc')` to 1. — Alex, Apr 25 '17 at 02:44
One book may have over 5k chapters, should I fetch all them, memory issue? — trinvh, Apr 25 '17 at 02:46
`$q->orderBy('updated_at', 'desc')->limit(1);` doesn't work? — Alex, Apr 25 '17 at 02:49
Yes, I also tried to create a new function in Book model (like above), it doesn't work too. — trinvh, Apr 25 '17 at 02:51
Ok how about `$q->orderBy('updated_at', 'desc')->take(1)->skip(1);`? From the link above. — Alex, Apr 25 '17 at 02:56
@Alex You can't add a `limit` to an eager loaded relationship. Say you have 10 books, and each book has 10 chapters (100 chapters total). If you add `limit(1)` to the eager loaded `chapters` relationship, this will make the relationship query fetch 1 chapter total, not 1 chapter per book. — patricus, Apr 25 '17 at 03:00

patricus · Accepted Answer · 2023-01-09T16:56:50.810

29

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

edited Jan 09 '23 at 16:56

answered Apr 25 '17 at 02:57

patricus

59,488
15
143
145

Strange, I tried this before with function lastest_chapter() { return $this->chapters()->take(1); } and it doesn't work. In your post, it should be hasMany and limit result with take(1). Thank for your answer! – trinvh Apr 25 '17 at 03:00
@trinvh I specifically made it a `hasOne` relationship. There is only one latest chapter, not many. – patricus Apr 25 '17 at 03:02
Ah, but in my situation, I have to filter by sort_order column. Anyway, thanks! – trinvh Apr 25 '17 at 03:04
@trinvh `latest()` is just a shortcut for `->orderBy('created_at', 'desc')`. You can add whatever conditions you need to the relationship. e.g. `return $this->hasOne(Chapter::class)->orderBy('sort_order', 'desc');` – patricus Apr 25 '17 at 03:07
4

This is not the correct answer because it's not working as expected. The wrong SQL query was generated that fetch all chapters instead of one chapter per book – trinvh May 03 '17 at 16:31
@trinvh This is the correct answer for the problem you've described. If something isn't working as expected, please show what you've implemented and what you believe is incorrect. – patricus May 03 '17 at 16:43
Maybe you wanna try to make something similar and look at mysql logs. My question is not clear enough, I want to optimize performance if mysql server and web server are different. In above code, mysql join all chapters and Laravel (ORM) takes one chapter, when the query is executing, it returns a big data (stories and all chapters belong to them) to the web server and takes a long time to completed. I think this is MySQL limitation. ANW, i marked answer correctly again. – trinvh May 04 '17 at 10:04
Laravel introduced `latestOfmany()` in latter versions. You should be using it, `latest()` produces inconsistent results – Skywarth Jan 09 '23 at 15:17

score 11 · Answer 2 · answered Jun 29 '21 at 08:06

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

score 1 · Answer 3 · answered Oct 12 '21 at 08:44

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

Laravel eloquent get the latest row of related table

3 Answers3

Laravel >= 8.42.0

Laravel < 8.42.0

Linked