How to create a custom DQL function in Symfony

Doctrine Query Language (DQL) functions are built-in functions that allow you to manipulate data or perform operations on the results of a DQL query.

Some commonly used DQL functions include:

1. COUNT — returns the number of results in a query
2. SUM — calculates the sum of columns in a query
3. AVG — calculates the average value of a column in a query

DQL functions are particularly useful for building complex queries in Doctrine ORM. For example, you might use them to calculate statistics or to transform data in various ways. For instance, you could use a DQL function to concatenate multiple columns into a single field or to extract a substring from a field. Using these functions, you can get the precise results you need for your application.

Although there are many MySQL functions that are not directly available in Doctrine, it is still possible to use them in your development. One way to do this is by defining your own DQL functions using Doctrine’s User-Defined Functions API (UDF). This API allows you to extend the functionality of DQL by registering custom functions that can be used in your queries.

In this article, we’ll take a closer look at the UDF API and how you can use it to expand the querying capabilities of Doctrine. We’ll also discuss the practical examples to assist you in defining and registering your functions. Ultimately, you’ll acquire a comprehension of using the UDF API to improve your Doctrine development.

Prerequisites

Install these packages in your Symfony project.

# composer.json
"require": {
  doctrine/doctrine-bundle
  doctrine/doctrine-migrations-bundle
  doctrine/orm
}

Configuration

DQL functions can be classified into three types:

1. String
2. Numeric
3. DateTime

When creating custom DQL functions, it is essential to register them under one of these categories, depending on their operation nature. In the Symfony framework, custom DQL functions can be defined and registered by modifying the doctrine configuration settings. For instance, the syntax for registering custom DQL functions in Symfony may look like this:

# config/packages/doctrine.yaml
doctrine:
    orm:
        # ...
        dql:
            string_functions:
                test_string: App\DQL\StringFunction                
            numeric_functions:
                test_numeric: App\DQL\NumericFunction
            datetime_functions:
                test_datetime: App\DQL\DatetimeFunction

Note: It is important to note that custom DQL functions in Doctrine are instantiated separately from the Symfony service container. This means that services or parameters cannot be injected into a custom DQL function. The function is executed independently from the rest of the Symfony application and cannot access the Symfony container. Therefore, any dependencies the function requires must be passed in as arguments rather than injected through the container.

How to register the DQL function?

Add your functions to the list of ORM functions that recognize and can use during its operations. Below you can find the ORM configuration:

# config/packages/doctrine.yaml
doctrine:
    orm:
        # ...
        dql:
            string_functions:
                FIRST: App\DBAL\FirstFunction    
            

The FIRST is the name of the function that will be referred to in the DQL query. App\DBAL\FirstFunction is a string of a class name that has to extend Doctrine\ORM\Query\AST\Functions\FunctionNode. This class provides the required APIs and methods to create a UDF (User-Defined Function).

<?php

namespace App\DBAL;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Subselect;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

/**
 * FirstFunction ::=
 *     "FIRST" "(" Subselect ")"
 */
class FirstFunction extends FunctionNode
{
    /**
     * @var Subselect
     */
    private $subselect;

    /**
     * {@inheritdoc}
     */
    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->subselect = $parser->Subselect();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    /**
     * {@inheritdoc}
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        return '(' . $this->subselect->dispatch($sqlWalker) . ' LIMIT 1)';
    }
}

The DQL parser is a tool used to parse DQL queries and generate an Abstract-Syntax Tree (AST) using a top-down recursive descent approach. This AST is then processed using a TreeWalker approach to generate the corresponding SQL statements for execution. This approach makes the DQL parser code more readable and manageable, even in larger and more complex applications, by breaking it down into smaller, more manageable sections. This also makes it easier to troubleshoot issues, debug errors, and modify the code. The TreeWalker approach enables the parser to generate the appropriate SQL statements for a given query by analyzing the structure and content of the AST.

To implement a custom function in the DQL parser, you can use the FunctionNode class. This class requires you to implement two methods: parse for the parsing process called parse and getSql()for the TreeWalker process.

By implementing these methods, you can define custom functions that can be used in DQL queries. An example of a custom function might be one that performs a mathematical operation on a specific column in a database table.

How to use this DQL Function?

Let’s take an example here: we have two entities, Country and City. In a country list query, if we want to retrieve the first city from the multiple-city results, we can use the custom FIRST function.

To implement this in your CountryRepository class function, you can write the following code:

$this->createQueryBuilder('country')
->select('country as Country')
->addSelect('FIRST(SELECT city.name FROM App:City AS city WHERE city.country = country.id GROUP BY city.name) AS cityName')
->getQuery()
->getResult()

Key takeaway

With Doctrine Query Language (DQL), you can create extensions that add new vendor-specific function packages, such as additional math functions or hashing functions.

What’s Next

The current version of Doctrine’s Object-Relational Mapping (ORM) has a well-established set of functions. However, future versions will re-evaluate whether to abstract and add more vendor-specific SQL functions to the DQL language’s scope. The code for extending DQL and other Doctrine extensions is available on GitHub, and the community is encouraged to contribute to further enhance the capabilities of Doctrine.

To stay up-to-date with the latest trends in the development ecosystem, follow Simform Engineering.