How to register custom DQL functions (doctrine extensions) in Symfony 4

The DQL parser of Doctrine 2 has hooks to register functions that can then be used in your DQL queries and transformed into SQL, allowing to extend Doctrines Query capabilities, instead of writing raw SQL. In this article, we'll explain you briefly how to configure a custom doctrine extension on your Symfony 4 project.

1. Create/choose your DQL extension

As first step, create a directory in the src directory of your Symfony project namely DQL. This directory will store the classes that you want to register as custom doctrine extensions. Inside this directory, the namespace of the files stored inside should be App\DQL. In our case, we want to register 2 custom text extensions for doctrine (match against and soundex), so we will have 2 files, our structure will look like this:

The code of the MatchAgainst function and file MatchAgainst.php that we will register is the following (for you only the namespace is important):

namespace App\DQL;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

/**
 * "MATCH_AGAINST" "(" {StateFieldPathExpression ","}* InParameter {Literal}? ")"
 */
class MatchAgainst extends FunctionNode {

    public $columns = array();
    public $needle;
    public $mode;

    public function parse(\Doctrine\ORM\Query\Parser $parser) {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        do {
            $this->columns[] = $parser->StateFieldPathExpression();
            $parser->match(Lexer::T_COMMA);
        } while ($parser->getLexer()->isNextToken(Lexer::T_IDENTIFIER));
        $this->needle = $parser->InParameter();
        while ($parser->getLexer()->isNextToken(Lexer::T_STRING)) {
            $this->mode = $parser->Literal();
        }
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
        $haystack = null;
        $first = true;
        foreach ($this->columns as $column) {
            $first ? $first = false : $haystack .= ', ';
            $haystack .= $column->dispatch($sqlWalker);
        }
        $query = "MATCH(" . $haystack .
                ") AGAINST (" . $this->needle->dispatch($sqlWalker);
        if ($this->mode) {
            $query .= " " . $this->mode->dispatch($sqlWalker) . " )";
        } else {
            $query .= " )";
        }
        
        return $query;
    }

}

Once you have a valid extension, proceed to register it in the next step.

2. Register DQL extension

To register DQL functions in Symfony 4, you can just register them specifying the classes in the respective block under the dql block of orm in doctrine. Remember that in Doctrine, there are only three types of functions in DQL, those that return a numerical value, those that return a string and those that return a Date, so there must be a major block that contains every type of function:

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

In our case, with our MatchAgainst and Soundex functions, that handle text, we would simply register them with the following snippet in the doctrine.yaml file under the string_functions node:

# app/config/packages/doctrine.yaml
doctrine:
    # Under orm, create the DQL
    orm:
        #
        # ...
        # 
        dql:
            # Register string functions with the correct namespace
            string_functions:
                MATCH_AGAINST: App\DQL\MatchAgainst
                SOUNDEX: App\DQL\SoundexFunction

For explicitly declared entity managers

In case that you use multiple entity managers in Doctrine, you may register the custom functions specifically for some of them inside the manager block instead:

# config/packages/doctrine.yaml
doctrine:
    orm:
        # ...
        entity_managers:
            example_manager:
                dql:
                    string_functions:
                        MATCH_AGAINST: App\DQL\MatchAgainst

Happy coding !