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.
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.