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)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more