Querying translations | laravel-translatable | Spatie SPATIE Laravel Translatable ======================= spatie.be/open-source [Docs](https://spatie.be/docs) [Laravel-translatable](https://spatie.be/docs/laravel-translatable/v6) Basic-usage Querying translations Version v6 Other versions for crawler [v6](https://spatie.be/docs/laravel-translatable/v6) Querying translations - [ Introduction ](https://spatie.be/docs/laravel-translatable/v6/introduction) - [ Support us ](https://spatie.be/docs/laravel-translatable/v6/support-us) - [ Requirements ](https://spatie.be/docs/laravel-translatable/v6/requirements) - [ Upgrading ](https://spatie.be/docs/laravel-translatable/v6/upgrading) - [ Installation & setup ](https://spatie.be/docs/laravel-translatable/v6/installation-setup) - [ Questions and issues ](https://spatie.be/docs/laravel-translatable/v6/questions-issues) - [ Changelog ](https://spatie.be/docs/laravel-translatable/v6/changelog) - [ About us ](https://spatie.be/docs/laravel-translatable/v6/about-us) Basic usage ----------- - [ Getting and setting translations ](https://spatie.be/docs/laravel-translatable/v6/basic-usage/getting-and-settings-translations) - [ Removing translations ](https://spatie.be/docs/laravel-translatable/v6/basic-usage/removing-translations) - [ Replacing translations ](https://spatie.be/docs/laravel-translatable/v6/basic-usage/replacing-translations) - [ Querying translations ](https://spatie.be/docs/laravel-translatable/v6/basic-usage/querying-translations) - [ Validating translations ](https://spatie.be/docs/laravel-translatable/v6/basic-usage/validating-translations) - [ Handling missing translations ](https://spatie.be/docs/laravel-translatable/v6/basic-usage/handling-missing-translations) Advanced usage -------------- - [ Available events ](https://spatie.be/docs/laravel-translatable/v6/advanced-usage/available-events) - [ Customize the toArray method ](https://spatie.be/docs/laravel-translatable/v6/advanced-usage/customize-the-toarray-method) - [ Usage with factories ](https://spatie.be/docs/laravel-translatable/v6/advanced-usage/usage-with-factories) Querying translations ===================== If you're using MySQL 5.7 or above, it's recommended that you use the JSON data type for housing translations in the db. This will allow you to query these columns like this: ``` NewsItem::where('name->en', 'Name in English')->get(); ``` Or if you're using MariaDB 10.2.3 or above : ``` NewsItem::whereRaw("JSON_EXTRACT(name, '$.en') = 'Name in English'")->get(); ``` If you want to query records based on locales, you can use the `whereLocale` and `whereLocales` methods. ``` NewsItem::whereLocale('name', 'en')->get(); // Returns all news items with a name in English NewsItem::whereLocales('name', ['en', 'nl'])->get(); // Returns all news items with a name in English or Dutch ``` If you want to query records based on locale's value, you can use the `whereJsonContainsLocale` and `whereJsonContainsLocales` methods. ``` // Returns all news items that has name in English with value `Name in English` NewsItem::query()->whereJsonContainsLocale('name', 'en', 'Name in English')->get(); // Returns all news items that has name in English or Dutch with value `Name in English` NewsItem::query()->whereJsonContainsLocales('name', ['en', 'nl'], 'Name in English')->get(); ``` To search for a partial value in one or more translated columns across locales, use `whereTranslationLike`: ``` NewsItem::query()->whereTranslationLike(['name', 'body'], 'news', ['en', 'nl'])->get(); ``` The scope matches any of the requested columns and locales, and groups these matches so they can be combined with other query constraints. It searches translated values, not locale keys. When locales are omitted, it uses the locales configured via the `Spatie\Translatable\Facades\Translatable` facade, falling back to the model's current locale: ``` Translatable::locales(['en', 'nl']); NewsItem::query()->whereTranslationLike('name', 'news')->get(); ``` Explicit locales override the configured defaults. Passing an empty columns or locales array matches no records. Both the translated value and the search pattern are lowercased by the database. Unicode case conversion depends on your database and collation; SQLite's built-in `lower()` only converts ASCII characters. Non-ASCII values can still be searched, but their letter case must match on SQLite unless Unicode-aware functions are installed. The search value is bound as a parameter and wrapped in `%` wildcards. Any `%` and `_` characters in the value retain their SQL `LIKE` wildcard meaning. An empty search matches any non-null translated value in the requested columns and locales. Columns and locales use Laravel's JSON selector syntax and should be application-defined, not taken directly from untrusted request input. A good match? ------------- ### What we do best - All things Laravel - Custom frontend components - Building APIs - AI-powered features - Simplifying things - Clean solutions - Integrating services ### Not our cup of tea - WordPress themes - Cutting corners - Free mockups to win a job - "Just execute the briefing" In short: we'd like to be a **substantial part** of your project. [ Get in touch via email ](mailto:info@spatie.be?subject=A%20good%20match%21&body=Tell%20us%20as%20much%20as%20you%20can%20about%0A-%20your%20online%20project%0A-%20your%20planning%0A-%20your%20budget%0A-%20%E2%80%A6%0A%0AAnything%20that%20helps%20us%20to%20start%20straightforward%21)