Level 2
The example you provided should do the work for you. What is the essence of the error you're getting? What's exactly wrong with your accessor using the relation's attribute?
May 5, 2019
4
Level 1
Ordering results by relationship field without fetching all records
Hello, I'm building an API using Laravel and facing a problem with ordering model results by a relationship field.
Let's imagine I have a User model, and a Profile model. Profile belongs to a User. Profile table has an 'age' field.
Can I order Users by the age field in Profile table and paginate result without fetching all Users from the database?
As for now, the only option I know is to use the raw query with join:
User::join('profiles', 'users.id', '=', 'profiles.user_id')
->orderBy('profiles.age')->paginate(10);
However, this option gives me an error because I have some appended accessors in my User model, invoked by $this->profile->age keywords.
I could also use ->get()->sortBy() function. But in this case pagination is not available, it will receive all records from the database.
Is there any way to order results by a relationship field using Eloquent?
Level 1
@DAVIDPETROV - Well, I don't know what exactly caused the error, it tried to get the relationship without success, but I fixed it selecting only one attribute from the joined table: ->select('users.*', 'age')
Now the final query looks like this:
User::join('profiles', 'users.id', '=', 'profiles.user_id')->select('users.*', 'age')
->orderBy('profiles.age')->paginate(10);
It joins only one field, and sorts table by this field. All other information from the profile can be loaded as nested relationship, so it will not break any front-end logic.
Level 122
perhaps you also need to load the profile so that you still have the profile for the 10 users loaded
User::join('profiles', 'users.id', '=', 'profiles.user_id')
->orderBy('profiles.age')
->with('profile')
->paginate(10);
1 like
Level 122
make sure you don't have an n+1 issue if you dont eager load the profile
Please or to participate in this conversation.