Laravel 4.1: How to Paginate Eloquent Eager Relationship

Laravel 4.1: How to paginate eloquent eager relationship?

To clarify something: paginating eager-loaded relationships is somewhat of a misconception. The point of eager loading is to retrieve all relationships in as few queries as you can. If you want to retrieve 10 topics, all of which have 35 posts, you will only need two queries. Sweet!

That said, paginating an eager-loaded relationship is not going to work. Consider two scenarios when eager loading happens:

1. You want to retrieve and list topics, and maybe list the first five posts for each topic. Great! Eager loading is perfect. Now, you wouldn't want to paginate the eager-loaded posts on a page like this, so it doesn't matter.

2. You load a single topic, and you want to paginate the posts for that topic. Great! Relatively easy to do. However, if you've already eager-loaded all posts belonging to this topic, you've just potentially retrieved a lot of extra resources that you don't need. Therefore eager loading is actually hurting you.

That said, there are two potential solutions:

Option 1: Create a custom accessor that paginates the Eloquent relationship.

/**
 * Paginated posts accessor. Access via $topic->posts_paginated
 * 
 * @return \Illuminate\Pagination\Paginator
 */
public function getPostsPaginatedAttribute()
{
    return $this->posts()->paginate(10);
}

Pros: Paginates very easily; does not interfere with normal posts relationship.

Cons: Eager loading posts will not affect this accessor; running it will create two additional queries on top of the eager loaded query.

Option 2: Paginate the posts Collection returned by the eager-loaded relationship.

/**
 * Paginated posts accessor. Access via $topic->posts_paginated
 * 
 * @return \Illuminate\Pagination\Paginator
 */
public function getPostsPaginatedAttribute()
{
    $posts = $this->posts;

    // Note that you will need to slice the correct array elements yourself.
    // The paginator class will not do that for you.

    return \Paginator::make($posts, $posts->count(), 10);
}

Pros: Uses the eager-loaded relationship; creates no additional queries.

Cons: Must retrieve ALL elements regardless of current page (slow); have to build the current-page elements manually.

Pagination and Eager Loading in Laravel

If you need to specify the query for subtopics, you should eager load your resources models from within that query rather than eager loading subtopics again.

Try this:

$topics = Topic::with(['subtopics' => function($query){
    $query->with('resources')->orderBy('glc_subtopic_priority','asc');
}])->find($id)->paginate(1);

return View::make('subtopics.index', ['topics' => $topics]);

Laravel Eloquent: Order results by eaged loaded model.property, and paginate the result

You should be able to use both join and with.

Post::select('posts.*') // select the posts table fields here, not categories
->with('category','category.tags')
->join('categories','posts.category_id','=','categories.id')
->orderBy('categories.name)
->paginate(10);

remember, the with clause does not alter your query. Only after the query is executed, it will collect the n+1 relations.

Your workaround indeed loses the eager loading benefits. But you can call load(..) on a collection/paginator (query result) as well, so calling ->paginate(10)->load('category','category.tags') is equivalent to the query above.

Paginating Eager loaded relationships

i thought of 2 methods

1st method

//here returned as Illuminate\Database\Eloquent\Collection not Illuminate\Pagination\LengthAwarePaginator so you need to make an extra logic to get current and next page from max relations etc.
to access it route('your_route', ['pagination_name'=>1])

$sales  = Drinks::with(['users' => function ($filter) {
  $filter->paginate(50, ['*'], 'pagination_name');
}])
->where('id', $drink_id)->first(); //latest() from unique id is first() or Drinks::find($drink_id);

i suggest this method, easier i guess

$sales  = Drinks::find($drink_id);
$sales->setRelation('user', $sales->users()->paginate(50)); //you can also set pagination name here

to use it : primary info use $sales, then to get pagination links use $sales->user->links() and $sales->user to list current paginated users

the rest is up to you to change the functionality

---

addition for response, in your model you can set attributes:

class User extends Model
{
  protected $with = ['profile'];
...

Laravel orderBy with paginate and relations

What i have done in this situations that i want to sort based on a column of a relationship is that i call the model of the relationship to sort it:

So let's say that your query is:

$myQuery = $model->with('employee')->where('this_column','=','that_column');

After comes the sorting part. For the example let's assume that the model of your relationship is called Employee

    $myQuery->orderBy(
        Employee::select('id')
            ->whereColumn('employee_id', 'employee.id')
            ->orderBy('PRSVORNAME', 'asc')
            ->limit(1),
        'asc'
    );

return $myQuery->get();

After that you can add your pagination or whatever you like since your collection is already sorted after the ->get()

For the above example i assumed that the foreign key between the two tables is the id column. In case it's another column you can match it in the whereColumn

---

Related Topics