Download the PHP package kaveraa/unaccent-search without Composer

Unaccent Search

English - Français

SQL search without accents and without case for Laravel and Symfony / Doctrine.

When a user types eleve, the search finds Élève, ÉLÈVE and élève. creme brulee finds Crème brûlée. oeuvre finds Œuvre.

• No database extension: you do not need unaccent or a special collation.
• MySQL, MariaDB, PostgreSQL and SQLite: the same code works on all of them.
• Easy to install: with Laravel, composer require is enough. With Symfony, add one line in bundles.php.
• Secure: the search term is always a bound parameter, column names are checked, and % and _ typed by the user are searched as normal characters.
• An empty search does not filter: you can pass $request->search directly, even when it is null.

Contents

• Installation
• Laravel
• Symfony
• Doctrine without Symfony
• Search modes
• Add characters
• Use the normalizer alone
• How it works
• Limits
• Development

Installation

Requirements: PHP 8.2 or more, with the mbstring extension.

Laravel

Setup

Nothing to do. Laravel finds the service provider automatically.

Search in one column

The methods are added to the Query Builder. So they also work with Eloquent models, relations, sub-queries and DB::table():

Search in several columns (global search)

whereAnyLikeUnaccent finds the rows where at least one column matches. The conditions are grouped in parentheses, so the next where applies to the whole group:

All methods

$mode says how the term must match: contains (default), starts with, ends with or equal. See search modes.

Accepted columns

Warning: a DB::raw() expression is put in the query as it is: never put user input in it. Text column names are checked. An invalid name throws an InvalidArgumentException.

Example: search form

The mode can come directly from the request, checked with Rule::enum():

Configuration (optional)

To add characters to the replacement table:

Auto-completion in your IDE

The methods are found by Laravel Idea and by barryvdh/laravel-ide-helper (php artisan ide-helper:generate).

Symfony

Setup

Add the bundle in config/bundles.php:

That is all. The bundle adds the DQL function UNACCENT to Doctrine. You do not need to change doctrine.yaml, even with several entity managers.

Search in a repository

The UnaccentQuery class adds the conditions to a Doctrine QueryBuilder:

All methods

The parameters are bound automatically, with unique names. So you can call several methods on the same QueryBuilder.

With condition(), you can build your own expressions:

Write DQL yourself

You can use the UNACCENT() function anywhere in DQL. Build the parameter with Normalizer::pattern() and keep ESCAPE '!':

Configuration (optional)

Doctrine without Symfony

Add the DQL function to the ORM configuration, then use UnaccentQuery like above:

With SQLite and a Doctrine query cache, also add the middleware. It prepares each SQLite connection (it does nothing on other databases):

Search modes

You can give the mode as a Kaveraa\UnaccentSearch\Mode enum or as text.

Add characters

The default table covers French and the most common European accents:

Upper case letters work automatically: É is first changed to lower case, then replaced.

For other alphabets, add entries with the configuration (Symfony) or directly in PHP, when the application starts:

With MySQL, MariaDB and PostgreSQL, each entry adds one REPLACE() to the query. Only add the characters that are in your data.

Use the normalizer alone

The core of the package does not need any framework:

SqlExpression::wrap() gives the normalized SQL expression of a column for one database. Use it if you write your queries yourself:

With SQLite, the expression calls the unaccent_search() function. Register it once on your connection (the Laravel and Doctrine parts of the package do it for you):

How it works

The search compares two values that are normalized in the same way:

1. In PHP, the search term is changed to lower case, the accents are replaced, and the % and _ characters are escaped: Élève becomes %eleve%.
2. In SQL, the column goes through the same replacement table: REPLACE(REPLACE(LOWER(name), 'é', 'e'), 'è', 'e').... SQLite does not accept too many nested REPLACE(), so with SQLite a PHP function unaccent_search() does the same work. It is registered on the connection automatically.
3. The comparison uses LIKE ? ESCAPE '!'. The term is always a bound parameter.

The same table is used on both sides. So if a character is not in the table (for example č), it is searched as it is, and the search still finds it.

Limits

• Performance. The expression on the column blocks the use of an index: the database reads the whole table. This is not a problem up to a few hundred thousand rows. For bigger tables, store a normalized column (filled with Normalizer::normalize()), add an index on it, and search in it.
• MySQL / MariaDB. With a utf8mb4_*_ci collation (the default), MySQL already ignores some accents in LIKE. The package then gives the same results, or a few more. With a _bin or _cs collation, only the package makes the search ignore accents.
• Supported databases. MySQL, MariaDB, PostgreSQL and SQLite. Another database (SQL Server, Oracle) throws an InvalidArgumentException.

Development

By default, the tests use SQLite in memory (you need the pdo_sqlite extension). To test the other databases, start them with Docker, then choose one with UNACCENT_DB:

GitHub Actions runs the tests on the four databases and on several PHP versions.

To propose a change (branch, tests, rules, Pull Request), read the CONTRIBUTING.md guide.

See the CHANGELOG for the list of versions.

License

MIT. See LICENSE.