laravel
laravel copied to clipboard
laravel-json-api
Sorting for relationships
No. It might be possible to support this in the future but it's currently not a priority. The workaround is to sort the relationship client-side.
I found myself needing a feature like this. It would be neat if this worked. Sorting client-side is not possible for paginated results.
Str::make('email')->on('user')->sortable();
If you're doing pagination, presumably you're retrieving the resources from a relationship endpoint? In which case you can just use the JSON:API sort parameters.
E.g. if you have a posts resource with a comments relationship. You'd be retrieving a paginated relationship via /api/v1/posts/123/comments at which point you can use sort parameters for the comments resource.
E.g. /api/v1/posts/123/comments?page[number]=1&sort=-createdAt
Unless I'm missing something about what you're doing?
@lindyhopchris your solution would work for sorting the relationship collection on it's own, but not for sorting the main resource based off the relation value. For instance assume we have a users table and they all have a relationship to the company they work for which is a separate model. Now you want to show users full names and company names in the same table and allow the end user to sort by either field. This does not seem easily possible currently. The only solution I found was using a custom sort and writing inner joins manually. This is less than ideal. In a perfect world the JSON API spec dot notation for related models would work.
from the JSON API Docs:
Note: It is recommended that dot-separated (U+002E FULL-STOP, “.”) sort fields be used to request sorting based upon relationship attributes. For example, a sort field of author.name could be used to request that the primary data be sorted based upon the name attribute of the author relationship.
Hope this helps clarify. Any suggestions are welcomed.
@freestyledork can you provide an example of how you'd sort by a relationship using Eloquent? I haven't done it before and could really do with an example of how it works in Eloquent, i.e. ignoring the JSON:API implementation and how you'd do it if you're writing an Eloquent query.
@lindyhopchris Sure, here is an example.
To sort Laravel Eloquent results based on a related model's column, you'll need to use a join clause, because ordering results on the basis of related model's column cannot be done directly. Let's say User belongs to Company and Company has a name. So, we can sort users based on name of the company they belong to in the following way:
use App\Models\User;
use Illuminate\Support\Facades\DB;
$users = User::join('companies', 'companies.id', '=', 'users.company_id')
->orderBy('companies.name')
->select('users.*') // Avoid 'column name is ambiguous' errors
->get();
In this case, we're joining the companies table based on the foreign key company_id of the users table and then we are ordering the results by the name of the company. Please remember to always call select() method by specifying table for the columns when you join two tables, to avoid 'column name is ambiguous' errors to the columns which are present in both tables.
Some considerations, table relations are typed in this example, but it should be possible to generate based on the main models relation. $model->getKeyName(); and $relation->getForeignKeyName(); This becomes more complicated when looking for results using a pivot table or collection. Perhaps there is a way to get the query another way that would include any specific additional where clauses on a relationship.
Working with a collection example of users have posts and we want to sort users based on the last post activity.
$users = User::leftJoin('posts','users.id','=','posts.user_id') // Use left join to avoid only loading models with at least one post
->orderBy('posts.created_at', 'desc')
->select('users.*')
->get();
If we take it a step further and users have a relation with some predetermined scope or filter the above methods might not work. Assuming the user and post relation, maybe we want to have a relation on the user for draftPosts(). This would be a HasMany() Relation with a where clause attached to it.
draftPosts(): HasMany
{
return $this->posts()->where('draft','=',true);
}
In the above example, the additional where clause would be lost if we just do a plain join without considering established queries. I haven't done enough research to see if it's possible to just get the query and add it back after the join.
I am open to any feedback you might have on solving this issue.