Mapping attribute to another name in sort order

[pedrosalpr]

I would like to know how do I sort by an attribute that is mapped differently in the database?

In my database, the km field exists, and when I return to the user I use classResource and return this field as distance.

If it were to filter for example, this attribute, I would use Filter::custom.

use Spatie\QueryBuilder\Filters\Filter;
use Illuminate\Database\Eloquent\Builder;

class FiltersFreightsDistance implements Filter
{
    public function __invoke(Builder $query, $value, string $property) : Builder
    {
        return $query->where('km', '>', $value);
    }
}

use Spatie\QueryBuilder\Filter;

// GET /freights?filter[distance]=200
$freights = QueryBuilder::for(Freights::class)
    ->allowedFilters(Filter::custom('distance', FiltersFreightsDistance::class))
    ->get();

But how would I order by distance?

// GET /freights?sort=distance

Because if I order this way it gives an error:

SQLSTATE [42S22]: Column not found: 1054 Unknown column 'distance' in 'order clause'

[Indemnity83]

Interested in this too; slightly different use case but requires similar functionality. I have a table with version numbers (following symver) so basic SQL sorting doesn't result in the correct ouput.

What I do have though is a pre-calculated sort order column (that isn't presented in the api output), so when a user asks to sort by version I really want to sort by build at the server.

[sdebacker]

Custom sorting classes would be great to build this kind of queries:

$query->orderByRaw('`title`->>"$.fr" COLLATE utf8mb4_unicode_ci ASC')

Perfect for spatie/laravel-translatable, because MySQL’s JSON fields are collated to utf8(mb4)_bin, not useful for multilingual sorting. What do you think?

[spatie-bot]

Dear contributor,

because this issue seems to be inactive for quite some time now, I've automatically closed it. If you feel this issue deserves some attention from my human colleagues feel free to reopen it.