Nested relationship stops working after first result

Question

Here is the query I currently have:

$messages = Message::select('messages.*')
    ->where('post_id', 1)
    ->where('parent_id', 0)
    ->with(['children' => function ($query)
    {
        $query->take(3);
    }])
    ->paginate(10);

And here is the children relationship in my Message.php class:

public function children() {
    return $this->hasMany('App\Message', 'parent_id', 'id');
}

This is the output I'm trying to get:

- Message 1
    - Message 1.1
    - Message 1.2
    - Message 1.3
- Message 2
    - Message 2.1
    - Message 2.2
    - Message 2.3
- Message 3
    - Message 3.1
    - Message 3.2
    - Message 3.3
- etc

But instead, it's returning this:

- Message 1
    - Message 1.1
    - Message 1.2
    - Message 1.3
- Message 2
- Message 3
- etc

The query seems to only return the children for the first result, but no other result after.

What am I doing wrong? How can I fix my query to get the results I need?

Answer 1

First, to answer the question "What am I doing wrong?", if you follow the documentation for Eloquent eager loading, you'll see that the query you built translates to this:

select * from messages

select * from children where id in (1, 2, 3, 4, 5, ...) limit 3

(Notice that I made up the children table name, as I don't know what name you use.)

After retrieving the records, Eloquent will build its object, using exactly what you asked for: all the messages, and all their children - limited to 3 rows. This is why you only get the children for the first Message.

---

Now, if you want to stick with eager loading, you could simply get rid of the closure in the with method:

$messages = Message::select('messages.*')
    ->where('post_id', 1)
    ->where('parent_id', 0)
    ->with('children') /* the constraint is gone */
    ->paginate(10);

With this paginated result, you'll have access to all the Messages and all their respective children. Next, e.g. in the controller, you can modify your result by filtering it or manually limiting each children group to 3. Or, you do it quick and dirty and use a loop with a break directly in your view.

But don't do it quick and dirty.