How to Order Spatie Translatable Columns in Laravel

#php #laravel #webdev

Spatie utilizes the JSON column type to store localized texts, which poses a challenge when attempting to order the column using the "ORDER BY" clause in MySQL. However, there is an approach to overcome this limitation. By leveraging the JSON_EXTRACT function in MySQL, it is possible to order the JSON column effectively. In the case of using the Laravel framework, a custom scope can be implemented to achieve this functionality. The scope should accept the column name, sort direction, and optionally, the locale, providing flexibility in the ordering process. The following code snippet demonstrates the implementation of such a scope in Laravel:

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

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