Laravel orderBy with paginate and relations-CodePudding

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?

CodePudding user response：

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