Level 122
get the data and then sort the collection by the related model. You will need to do this with a closure.
Apr 3, 2017
10
Level 1
Order by eager loaded colum
Hi to all,
I'm having some trouble finding the right/best way to do one thing:
I've some case where I need to do the sorting based on a column that is loaded through eager loading. Here an example:
Account (id, name, surname) <-> Teacher (account_id)
Teacher::with('account')->orderBy('surname');
Usually the eager loading work great in those situation, where I need to access the the surname or name data. But in this situation where I need to order the data using that column, it doesn't work.
I've searched on the other topics and I haven't found a way using only the eager loading. I've found some suggestion about joining the accounts table, something like that:
Teacher::with("account")->join('accounts', 'teachers.id', '=', 'accounts.id')->orderBy('surname', 'DESC')
That work for the sorting, but have two downside:
- The data from accounts are loaded 2 times: first time from the joined query, and second time by the eager loading. Also, the data from the first query (the one with the join) is inserted into the Teacher model.
- I need to know the real name of a table out of the Model (usually in the controller)
There's any way/trick to do the eager loading/with but loading the data through the join? Or any other way to do sorting in those situation of eager loading? Any other suggestion?
Thanks to all
byez
Level 3
This is how I do it:
$teachers = Teacher::join('accounts', 'teachers.account_id', '=', 'accounts.id')->orderBy('accounts.surname', 'DESC')->select('teachers.*')->get();
then you can still access like such:
foreach ($teachers->accounts as $account) {
//
}
Though this only solves this problem: "Also, the data from the first query (the one with the join) is inserted into the Teacher model", but I couldn't find a better way.
1 like
Level 122
$teachers = Teacher::with('account')->get()->sortBy('account.surname');
Know that you are sorting the collection, and not affecting the SQL query.
To sort it in sql you would have to have a join. You can still eager load to get the nested relationship. It will still be two queries, the first with the joined Teacher/account and then for the account. If you don't want the extra fields showing in the teacher model then include ->select('teacher.*'). You can still sort by the surname even if it is not in the output.
2 likes
Level 1
Downside is, when you need to paginate the data. The sorting would only apply per page, you'd then get incorrect result.
Level 122
Yes if you sort the collection, which is why, if you want paginated results, you MUST use a join
Level 2
Teacher::with(['account' => function ($q) {
$q->orderBy('surname');
}]);
https://laravel.com/docs/5.4/eloquent-relationships#eager-loading just scroll to Constraining Eager Loads section.
Level 1
I know it's too late, but for someone who's dealing with the same case:
I guess you could do:
Teach::with('account')
->orderBy(Account::select('surname')->whereColumn('accounts.id', 'teachers.account_id'))
->paginate() // or get()
or even a query builder, much faster.
Reference: https://laraveldaily.com/post/order-by-belongsto-relationship-column-eloquent-vs-query-builder
Level 122
@jenky that only sorts the child, not the parent
Level 1
Thanks for the answers :)
I've done some testing with the various possibilities, and currently the method eager loading + collection sorting seam to take more time to load then the simple join (just with a small set of data).
So I'm trying a different approach (if someone have a similar case):
I've created a TeacherAccount model that extend the Teacher model, and work like the Teacher model, except that it's mean to have even the account data as attributes. So I know that when I'm using that particular model, I've both the teacher and account attribute in it. Here's an example of the model:
class TeacherAccount extends Teacher {
protected $table = "teachers";
public function getForeignKey()
{
return 'teacher_'.$this->primaryKey;
}
public function newQuery($excludeDeleted = true) {
return parent::newQuery()->join('accounts', 'teachers.id', '=', 'accounts.id');
}
}
What do you think of that solution? Any suggestions?
Thanks to all
Cheers
Level 122
Seems like a perfectly acceptable answer for a one-one relationship such as this.
Please or to participate in this conversation.