OrderBy when using Translatable fields (Spatie)

[beatwiz]

1. Create a Trait in \App\Traits

<?php

namespace App\Traits;

use App;
use DB;
use Illuminate\Database\Eloquent\Builder;

trait HasOrderByTranslatable
{
    public function scopeOrderByTranslatable(
        Builder $query,
        string $column,
        string $sortDirection = 'asc',
        string $locale = null
    ): Builder {
        $locale = $locale ?? App::currentLocale();

        return $query->orderBy(DB::raw("JSON_UNQUOTE(JSON_EXTRACT($column, '$.$locale'))"), $sortDirection);
    }
}

2. Add the trait to the Models

use HasOrderByTranslatable;

3. Column sorting

TextColumn::make('title')
          ->label('Title')
          ->sortable(query: fn(Builder $query, string $direction): Builder => $query->orderByRaw(
              "JSON_UNQUOTE(JSON_EXTRACT(title, '$.\"{$table->getLivewire()->activeLocale}\"')) $direction"
          ))
          ->searchable(),

4. Sorted Form Select relationship Options

Select::make('filters')
      ->relationship('filters', 'name',
          fn($query) => $query->where('type', 'study')->where('filter_type',
              'articles')->orderByTranslatable('name', 'asc', $form->getLivewire()->activeLocale))
      ->label('Articles'),

5. Sorted filter options

SelectFilter::make('subject')
            ->label('Subject')
            ->relationship('filters', 'name',
                fn($query) => $query->where('type', 'study')
                                    ->where('filter_type', 'subject')
                                    ->orderByTranslatable('name', 'asc',
                                        $table->getLivewire()->activeLocale)
            ),

SelectFilter::make('articles')
            ->label('Articles')
            ->relationship('filters', 'name',
                fn($query) => $query->where('type', 'study')
                                    ->where('filter_type', 'articles')
                                    ->orderByTranslatable('name', 'asc',
                                        $table->getLivewire()->activeLocale)
            ),