Sorting on translated field, ascending only. about nova-translatable HOT 6 CLOSED

Here is a trait, that is a possible solution for the problem:

<?php

namespace App\Nova\Traits;

trait SortTranslatableFields
{
    protected static function applyOrderings($query, array $orderings)
    {
        $locale = app()->getLocale();
        $translatableAttributes = app(self::$model)->make()->getTranslatableAttributes();

        foreach ($orderings as $column => $direction) {
            if (in_array($column, $translatableAttributes)) {
                unset($orderings[$column]);
                $query->orderByRaw("`{$column}` -> '$.{$locale}' {$direction}");
            }
        }

        return parent::applyOrderings($query, $orderings);
    }
}

Maybe this Trait could be added to the package, but you have to manually "use" it in every Nova resource, where you use Translatable fields.

from nova-translatable.

I made a macro. That way I can set it once and forget about it.

use Spatie\Translatable\HasTranslations;
use Illuminate\Database\Eloquent\Builder;


Builder::macro('orderBy', function ($field, $order = 'asc', $locale = null) {
            if (
                in_array(HasTranslations::class, class_uses($this->model))
                && in_array($field, $this->model->translatable)
            ) {
                $locale = $locale ?? app()->getLocale();
                $field .= '->' . $locale;
                $this->query->orderBy($field, $order);
            } else {
                $this->query->orderBy($field, $order);
            }
            return $this;
        });

from nova-translatable.

Feel free to send a PR that fixes this

from nova-translatable.

I think this has nothing to do with this package, the problem is how Nova handles the underlying json columns when sorting / searching.

MySQL isn't able to sort based on json column values with a simple ORDER BY column_name ASC/DESC.

The query should look something like this to work properly:
ORDER BY `column` -> '$."en"' DESC

from nova-translatable.

Closing this as it can't be handled in the package.

from nova-translatable.

Here is a trait, that is a possible solution for the problem:

<?php

namespace App\Nova\Traits;

trait SortTranslatableFields
{
    protected static function applyOrderings($query, array $orderings)
    {
        $locale = app()->getLocale();
        $translatableAttributes = app(self::$model)->make()->getTranslatableAttributes();

        foreach ($orderings as $column => $direction) {
            if (in_array($column, $translatableAttributes)) {
                unset($orderings[$column]);
                $query->orderByRaw("`{$column}` -> '$.{$locale}' {$direction}");
            }
        }

        return parent::applyOrderings($query, $orderings);
    }
}

Maybe this Trait could be added to the package, but you have to manually "use" it in every Nova resource, where you use Translatable fields.

For those who land here, trying to go the Trait way, on PostgreSQL, here is the syntax to access your JSON fields:
$query->orderByRaw("{$column}->>'{$locale}' {$direction}");
You may have to play with -> and ->> after the column name, or cast to string after accessing with ->

from nova-translatable.