DEV Community

Süleyman Özgür Özarpacı
Süleyman Özgür Özarpacı

Posted on

4

How to Order Spatie Translatable Columns in Laravel

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);
}
Enter fullscreen mode Exit fullscreen mode

In cases where the locale parameter is not provided, the scope automatically retrieves the current locale using the App::currentLocale() function.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

The best way to debug slow web pages cover image

The best way to debug slow web pages

Tools like Page Speed Insights and Google Lighthouse are great for providing advice for front end performance issues. But what these tools can’t do, is evaluate performance across your entire stack of distributed services and applications.

Watch video