Impossible to sort on relation field

[Samoht70]

Laravel Rest Api Version

2.5.1

Laravel Version

10.43.0

PHP Version

8.1

Database Driver & Version

No response

Description

I wanted to sort my users according to their role (I use laravel-spatie permission). I'm getting an error telling me that my field is invalid.

Here's the body of my request: { "search": { "sorts": [ {"field": "roles.id", "direction": "desc"} ] } }

After looking a little in the code I saw that it came from the SearchRules.php file. It's based on the fields in the current UserResource for me.

Do you think this will be implemented in the future?

Steps To Reproduce

ddzazda

[GautierDele]

This is laravel native feature, will need to see if it's on the route or no. Meanwhile I'll let it open

[sneakylenny]

Same goes for trying to sort by aggregated data. For example: Sort by posts by most comments. comments_count is not available as field.

More

Adding the aggregated field to the `fields()` method in your resource won't work either because when the sorts are applied, it prefixes the table to the field and it will throw: "Column not found: 1054 Unknown column 'posts.comments_count' in 'order clause'" because the table name is not prefixed to the aggregated select:

select `posts`.*, (
    select count(*) 
    from `comments` 
    where `posts`.`id` = `comments`.`post_id` 
    limit 50
) as `comments_count` # won't match the "order by `posts`.`comments_count`" instruction
from `posts` 
order by `posts`.`comments_count` # won't work with the `comments_count` aggregation
desc limit 50
offset 0