I ran into the following issue: I wrote a dynamic trait that generated an eloquent query. This works so far. But as the dataset can be extremly large (rows + possible relations) I want to paginate it. This is also no issue as long as i am not attempting to sort it. Sorting on the main model works also easily, however, when I want to sort based on the property of a relation (let's assume the relation is member and I want to sort based on the name) I run into issues.
Naivly I ignored the eager-loading of relations, of course this doesnt work. I also want to avoid joins as much as possible as I really just need the dataset with my relations.
return $model->sortBy('member.name')->offset(0)->take(50)->get();
Obviously doesnt work because I need to get() before sortBy. However rewriting this as
return $model->get()->sortBy('employee.PRSVORNAME')->offset(0)->take(50)->get();
Doesnt work either as I get a collection back which cant be paginated (note that I didnt include the pagination() here as this this results basically in the same issue as offset()->take()->get();
Is there a way to do this without having to use joins?
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