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

Question

Lets say I have three models: Post, Category and Tag.

The Post belongsTo Category and Category hasMany Post.

Theres manyToMany relation between Tag and Category.

I want to list my posts by Category name and paginate the results.

Post::with('category','category.tags')
  ->orderBy('category.name') //this is the bogus line
  ->paginate(10);

But this syntax doesn't work.

What I tried is this as:

Post::select('categories.*')
  ->join('categories','posts.category_id','=','categories.id')
  ->orderBy('categories.name)
  ->paginate(10);

But then I lose the eager loaded data.

If I drop the select() clause then I get rubbish data as categories.id overwrites posts.id. See here.

Is there any elegant way to solve this issue? ~~After spending hours on this I'm one step away from iterating through paginated posts and 'manually' loading the relations as:~~

foreach($posts as $post) {
  $post->load('category','category.tags');
}

Not even sure if there's downside to this but it doesn't seem right. Please correct me if I'm wrong.

UPDATE on last step: Eager loading on paginated results won't work so if I go that road I'll need to implement even uglier fix.

Did you tried `Post::with('category','category.tags')->select('categories.*') ->join('categories','posts.category_id','=','categories.id') ->orderBy('categories.name) ->paginate(10);` ?? — Maraboc, Jul 31 '17 at 14:08
Yes, as soon as I add `select()` none of the data specified in the `with()` method is not returned. I do get `category` but it's an empty array. — dbr, Jul 31 '17 at 14:14
the with wont work because `select('categories.*')` is wrong, you need the `posts` table fields instead. See my answer (updated now) — Otto, Jul 31 '17 at 14:16

Otto · Accepted Answer · 2017-07-31T14:17:40.137

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.

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

1 Answers1