How to use PostgreSQL ILIKE operator in Symfony 7 with Doctrine

In the last few days, I started a new side project with Symfony 7 to test the latest features of the framework. I decided as well to switch from the database engine that I always use with Symfony (MySQL) to PostgreSQL. I took this decision since PostgreSQL is known for handling multiple concurrent transactions pretty well, ensuring data integrity and complex transactional workflows. It's known as well for its performance with complex queries, especially when handling large datasets, so I decided to give it a try.

I launched a new docker environment with PostgreSQL, Apache 2.4, and PHP 8.3 and started working right away. After starting to develop something as simple as a text search feature, I discovered that my LIKE searches weren't working as expected (or at least as they used to work with MySQL).

After some research on why something like a simple LIKE search was failing, I discovered that the problem was caused because in PostgreSQL, the LIKE operator is case-sensitive, which means that if I have a table with the following row:

And I search with LIKE using a search term such as "col":

SELECT * FROM public.country
WHERE public.country.name LIKE '%col%'
ORDER BY id ASC 

There will be no results. This happens because the LIKE operator in Postgresql is case-sensitive as it compares character strings based on their real ASCII values, treating both lowercase and uppercase as distinct values. So if you want to perform a case-insensitive search in Postgresql all you need to do is use the ILIKE operator instead. In PostgreSQL, the query to find all the countries where the name looks like "col" is easily written like this using ILIKE:

SELECT * FROM public.country
WHERE public.country.name ILIKE '%col%'
ORDER BY id ASC;

The ILIKE operator performs the same pattern matching. Yet, it ignores the case, which makes it useful for scenarios where the text's case shouldn't impact the result of a query, just exactly what I need to search within a list of countries (exactly what I need).

After some research on the web looking for an alternative without too much hassle (as Doctrine doesn't support ILIKE, if you use it you will find the exception "Expected known function, got ILIKE) for simply searching in a table of countries without caring about the case of the search, many people just recommended using the LOWER function of SQL as a replacement for the ILIKE operator in PostgreSQL with something like this:

<?php

$term = 'col';

$q = $em->createQuery('
    SELECT * 
    FROM App\Entity\Country country 
    WHERE LOWER(country.name) LIKE :term
')->setParameter(':term', strtolower('%' . $term . '%'));
$result = $q->getResult();

This works of course, but this is not efficient at all. Applying the LOWER function on a column in PostgreSQL can impact negatively the performance of your queries, especially on large datasets as it requires processing each row to convert the values before matching it, preventing PostgreSQL from efficiently using indexes on the used column and leading to a slower query execution.

And that's how we come back to the beginning, How can I use ILIKE with my Doctrine queries? Certainly, Doctrine's DQL does not support ILIKE natively, but you can achieve case-insensitive matching using a custom function for that.

In this article, I will explain to you how to easily implement and use the ILIKE operator for Doctrine in your Symfony 7 project.

A. Using PostgreSQL ILIKE from a Doctrine package

The first option to implement ILIKE in your project is with a third-party package. The postgresql-for-doctrine package optimizes Doctrine's compatibility with PostgreSQL offering support for advanced data types and functions. One of the included functions is ILIKE, which is exactly what we need to execute case-insensitive queries.

To install this package run the following command in your terminal using composer:

composer require martin-georgiev/postgresql-for-doctrine

For more information about this package, please visit the official repository at GitHub here. After the installation, you need to register the ILIKE function in your doctrine.yaml file like this:

# app/config/packages/doctrine.yaml
doctrine:
    orm:
        dql:
            string_functions:
                ILIKE: MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\Ilike

Clear the cache and you will be able to use the ILIKE function in your doctrine queries:

// You can use ILIKE as a function in DQL comparing the result to true
// This will match all the rows in your table where the value of 'column' is similar to "Some search text"
$term = "Some search text";

$query->where('ILIKE(p.column, :term) = true')
    ->setParameter('value', '%' . $term . '%');

In the following example, you can find the logic that I needed to write to simply search a country in the countries table with an user given search term:

<?php

namespace App\Controller;

use App\Entity\Country;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;

#[Route('/ajax')]
class AjaxController extends AbstractController
{
    #[Route('/search-country', name: 'app_ajax_data')]
    public function searchcountry(
        EntityManagerInterface $entityManager
    ): Response
    {
        $term = "United states";

        // Countries will list all countries that match the search term "United states"
        $countries = $entityManager->getRepository(Country::class)
            ->createQueryBuilder('country')
            ->where('ILIKE(country.name, :term) = true')
            ->setParameter('term', '%' . $term . '%')
            ->getQuery()
            ->getResult()
        ;

        dump($countries);
        die();
    }
}

B. Create your own DQL function

If you're not willing to install a new package on your project as it may include too many functions that you don't need, you can achieve the same behavior by registering a custom DQL function that will implement ILIKE.

Through a DQL extension, you can use database-specific functions or custom expressions within the regular DQL queries. They make Doctrine more flexible and powerful, bridging the existing gap between DQL and SQL and letting you access advanced database functionalities without stopping using Doctrine.

To include the ILIKE DQL function in your project, create the DQL directory inside the src directory if it doesn't exist, and create the ILikeFunction.php file with the following content:

<?php

# src/DQL/ILikeFunction.php
namespace App\DQL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\TokenType;

class ILikeFunction extends FunctionNode
{
    public $field = null;
    public $value = null;

    public function parse(Parser $parser) : void
    {
        $parser->match(TokenType::T_IDENTIFIER);
        $parser->match(TokenType::T_OPEN_PARENTHESIS);

        $this->field = $parser->ArithmeticPrimary();
        $parser->match(TokenType::T_COMMA);

        $this->value = $parser->ArithmeticPrimary();
        $parser->match(TokenType::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker) : string
    {
        return sprintf(
            '%s ILIKE %s',
            $this->field->dispatch($sqlWalker),
            $this->value->dispatch($sqlWalker)
        );
    }
}

After creating the function, it's necessary to register the function in Doctrine by adding the path to the class in the doctrine.yaml file:

# app/config/packages/doctrine.yaml
doctrine:
    orm:
        dql:
            string_functions:
                ILIKE: App\DQL\ILikeFunction

Clear your project's cache and then you will be able to use the ILIKE function in your doctrine queries like this:

// You can use ILIKE as a function in DQL comparing the result to true
// This will match all the rows in your table where the value of 'column' is similar to "Some search text"
$term = "Some search text";

$query->where('ILIKE(p.column, :term) = true')
    ->setParameter('value', '%' . $term . '%');

Happy coding ❤️!