Writing Custom DQL Functions in Doctrine

1. Where DQL hits its limits

The Doctrine Query Language is deliberately built to be database agnostic, and that becomes exactly the problem once an application needs a specific SQL function of the database server in use. DQL supports only a limited set of built in functions like CONCAT, SUBSTRING or LOWER for good reason, because these work consistently across MySQL, PostgreSQL and other platforms. But once a project wants to use MySQL specific functions like JSON_EXTRACT for JSON columns or MATCH AGAINST for full text search, the built in function set of DQL is no longer enough.

At this point many developers reflexively reach for native SQL queries, abandoning Doctrine's abstraction entirely. That works, but it has drawbacks: result hydration must be configured manually with a ResultSetMapping, composing the query with other DQL parts becomes hard, and the query loses the type safety DQL otherwise provides. A custom DQL function solves exactly this dilemma: it makes a native SQL function available inside DQL without giving up the benefits of the QueryBuilder, parameter binding and automatic hydration.

The mechanism for this is Doctrine's FunctionNode API, which allows writing a class that translates DQL syntax into the corresponding native SQL syntax. This custom DQL function is then used like any built in function in WHERE, SELECT or ORDER BY clauses, including parameter binding and query cache compatibility.

2. The FunctionNode API at a glance

Every custom DQL function in Doctrine extends the abstract class Doctrine\ORM\Query\AST\Functions\FunctionNode. This base class defines two central methods that must be overridden: parse() and getSql(). The parse() method uses the DQL parser to read the function's arguments from the DQL string and translate them into AST nodes (Abstract Syntax Tree). The getSql() method takes these parsed arguments and generates the final SQL code embedded into the database query.

This separation is the core of the FunctionNode API: parsing and SQL generation are independent steps. That allows Doctrine to later reuse the same parsed DQL structure through the query cache without reparsing the function. A custom DQL function must additionally be registered with a unique name under which it can be called in DQL strings, for example JSON_EXTRACT(e.metadata, ':path') for a custom JSON extraction.

3. Registration in Symfony and doctrine.yaml

In Symfony, registering a custom DQL function happens centrally through the Doctrine bundle configuration, separated by the function's return type. String functions are registered under orm.dql.string_functions, numeric functions under orm.dql.numeric_functions, and datetime functions under orm.dql.datetime_functions. This separation matters because the DQL parser expects a matching return type depending on the context the function is used in.

# config/packages/doctrine.yaml
doctrine:
    orm:
        dql:
            string_functions:
                JSON_EXTRACT: App\Doctrine\DqlFunction\JsonExtract
                MATCH_AGAINST: App\Doctrine\DqlFunction\MatchAgainst
            numeric_functions:
                JSON_LENGTH: App\Doctrine\DqlFunction\JsonLength
                RAND: App\Doctrine\DqlFunction\Rand

Once registered, the custom DQL function is available wherever built in functions are allowed: in repository methods using QueryBuilder, in native DQL strings, and even in Doctrine filters. A cache clear is necessary after registration, because the DQL parser reads and caches the function list on first use.

4. Example: JSON_EXTRACT for JSON columns

A practical example of a custom DQL function is accessing JSON columns in MySQL. Many Symfony projects store flexible metadata in a JSON column, for example product attributes or feature flags. Without a custom DQL function, filtering inside this JSON structure is not possible without falling back to native SQL. With JSON_EXTRACT registered as a DQL function, this becomes possible directly in the QueryBuilder.

<?php

declare(strict_types=1);

namespace App\Doctrine\DqlFunction;

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

final class JsonExtract extends FunctionNode
{
    private Node $columnExpression;
    private Node $pathExpression;

    public function parse(Parser $parser): void
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->columnExpression = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->pathExpression = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker): string
    {
        return sprintf(
            'JSON_EXTRACT(%s, %s)',
            $this->columnExpression->dispatch($sqlWalker),
            $this->pathExpression->dispatch($sqlWalker),
        );
    }
}

With this custom DQL function registered, a repository method can write: ->andWhere("JSON_EXTRACT(p.attributes, :path) = :value"). Doctrine transparently translates that into native SQL, including correct parameter binding for :path and :value, without the query losing its type safety.

5. Example: full text search with MATCH AGAINST

A second common example of a custom DQL function is MySQL's full text search via MATCH ... AGAINST. This function uses a FULLTEXT index and additionally returns a relevance score that can be used for sorting. Since MySQL uses a special syntax here with multiple columns and a mode flag, the implementation is somewhat more complex than a simple two parameter function, but the underlying principle stays identical.

<?php

declare(strict_types=1);

namespace App\Doctrine\DqlFunction;

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

final class MatchAgainst extends FunctionNode
{
    /** @var Node[] */
    private array $columns = [];
    private Node $searchTerm;

    public function parse(Parser $parser): void
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->columns[] = $parser->StateFieldPathExpression();

        while ($parser->getLexer()->isNextToken(Lexer::T_COMMA)) {
            $parser->match(Lexer::T_COMMA);
            $this->columns[] = $parser->StateFieldPathExpression();
        }

        $parser->match(Lexer::T_COMMA);
        $this->searchTerm = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker): string
    {
        $columnSql = implode(', ', array_map(
            static fn (Node $column): string => $column->dispatch($sqlWalker),
            $this->columns,
        ));

        return sprintf(
            'MATCH(%s) AGAINST(%s IN NATURAL LANGUAGE MODE)',
            $columnSql,
            $this->searchTerm->dispatch($sqlWalker),
        );
    }
}

Registered as MATCH_AGAINST, the function can be used both in the WHERE clause for filtering and in the SELECT part for relevance sorting. This custom DQL function fully replaces a native query for full text search, without compromising composability or testability.

6. Lexer and parser: reading parameters correctly

Understanding the parser helper methods is essential to implementing a custom DQL function robustly. $parser->ArithmeticPrimary() parses an arbitrary arithmetic expression, including literals, parameters and path expressions, and suits generic arguments. $parser->StateFieldPathExpression(), on the other hand, explicitly expects a field path like p.name and is the right choice when an argument must guaranteedly reference an entity column, as with the columns in MATCH AGAINST.

The Lexer supplies the tokens the parser consumes. $parser->match(Lexer::T_COMMA) expects and consumes exactly one comma token, otherwise throwing a QueryException with a meaningful error message. Anyone writing a custom DQL function with a variable number of arguments must actively query the lexer for the next token, as shown in the MatchAgainst example with isNextToken(Lexer::T_COMMA). This pattern is the standard way to support optional or repeated arguments in Doctrine functions.

7. Testing custom functions in isolation

A custom DQL function can be tested in isolation without spinning up a full Symfony application. The test instantiates a minimal EntityManager with an in memory SQLite database or a real test database, registers the function directly through the Doctrine configuration, and executes a DQL query using the function. Comparing the generated SQL against the expected SQL string is a sensible first test, before verifying the actual query result against test data.

A common mistake with a custom DQL function only shows up in testing: if the function is registered in multiple contexts, for example as both a string and a numeric function simultaneously, the DQL parser can show inconsistent behavior. An integration test that calls the function in different clause positions, WHERE, SELECT and ORDER BY, reliably uncovers such edge cases before they cause hard to trace errors in production.

8. Alternatives: native queries and subqueries

Not every problem justifies a custom DQL function. For one off, rare reports, a native SQL query with ResultSetMapping is often faster to implement and easier to follow, especially when the query does not need to combine with other DQL building blocks anyway. For cases where the desired logic can already be expressed by combining existing DQL functions and subqueries, that path should be preferred before accepting the maintenance overhead of a custom FunctionNode class.

A custom DQL function pays off especially when the native SQL function is reused in many places across the project, needs to combine with other DQL expressions, or when QueryBuilder based composability matters for the codebase, for example in dynamically assembled filter queries in a search API.

9. DQL functions compared directly

The following table compares the different approaches to making native SQL functionality usable in a Doctrine based application, and shows the respective trade offs in maintainability and flexibility.

This comparison shows: a custom DQL function is the only approach that offers full composability with the rest of the QueryBuilder while keeping the ORM's automatic hydration. The one time implementation effort pays off quickly once the function is needed in more than one place in the project.

10. Summary

A custom DQL function closes the gap between the database agnostic Doctrine Query Language and the specific SQL functions of a concrete database like MySQL. Through the FunctionNode API with the methods parse() and getSql(), any native function, from JSON_EXTRACT to MATCH AGAINST, can be made transparently available in DQL, including parameter binding and composability with the QueryBuilder.

Registration happens centrally in doctrine.yaml, separated by the function's return type. It matters to weigh the effort of a custom DQL function against simpler alternatives like native queries: the effort pays off mainly for functions needed repeatedly across the project or combined with other DQL expressions. Isolated tests against the generated SQL string protect the implementation against regressions.

11. FAQ: Custom DQL Functions in Doctrine