Custom DQL usage with Symfony

Hello, in this article I will talk about the usage and benefits of Custom DQL in Symfony.

First of all, for those who are unfamiliar, I will explain Doctrine and then DQL. Those who are already familiar with this topic can skip this section.

Doctrine: It is an ORM (Object Relational Mapping) tool used for PHP applications. It provides us the ability to work in an object-oriented manner, abstracts basic SQL queries, and offers a more intuitive way to interact with database data.

DQL (Doctrine Query Language): It is a specialized query language that forms the foundation of Doctrine ORM. Although it has a structure similar to SQL, its most important feature is the ability to easily write relationships between objects.

An example of a DQL string:

SELECT c FROM Customer c JOIN c.orders o WHERE o.totalAmount > 100

As you can see above, we filtered by joining the Orders relation through the Customer Entity, and we did this in a simple and understandable language.

Now that we assume everyone has a basic understanding, let’s gradually move on to what Custom DQL is, what it’s used for, and how to write it.

Custom DQL commands are customized DQL commands. There might be functions that DQL doesn’t provide, and we may want to develop them ourselves. Let’s write a DQL that takes a given string, splits it, and returns the desired index as an example:

In Custom DQLs, there are two functions we need to pay attention to: getSql and parse. In the parse section, we need to specify the components of the function we are creating. The Lexer assists us in this; it recognizes different components (such as keywords, field names, operators, etc.) within the DQL query. It then breaks down these components into pieces for later interpretation and processing. Let's go through the process step by step.

First, let’s prepare an example DQL command:

“SELECT SPLIT_STR(‘a|bb|ccc|dd’, ‘|’, 3) FROM Product e”

Next, let’s see how this DQL command is processed in the parse() function:

$parser->match(Lexer::T_IDENTIFIER);

In the first step, we try to match our SPLIT_STR function; our functions are also referred to as an IDENTIFIER. When this process is successfully completed, it automatically moves on to the next token.

$parser->match(Lexer::T_OPEN_PARENTHESIS);

In the second step, we look for the opening parenthesis in our function, i.e., the opening parenthesis in SPLIT_STR(.

$this->stringExpression = $parser->StringPrimary();

In the third step, after the opening parenthesis, we parse the string we want to split, which is our first parameter.

$parser->match(Lexer::T_COMMA);

Then we match the , to look for the second parameter.

$this->delimiterExpression = $parser->StringPrimary();

We parse the delimiter we will use for splitting (|).

$parser->match(Lexer::T_COMMA);

We match the , again to look for the third parameter.

$this->positionExpression = $parser->ArithmeticPrimary();

We successfully parse the third parameter.

$parser->match(Lexer::T_CLOSE_PARENTHESIS);

Finally, we match the closing parenthesis ) to finish the parsing process.

After our parser is processed, we move on to the getSQL function. This function should return a string SQL statement:

public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf(
            'REPLACE(SUBSTRING(SUBSTRING_INDEX(%s, %s, %s), ' .
            'LENGTH(SUBSTRING_INDEX(%s, %s, %s - 1)) + 1), %s, \'\')',
            $this->stringExpression->dispatch($sqlWalker),
            $this->delimiterExpression->dispatch($sqlWalker),
            $this->positionExpression->dispatch($sqlWalker),
            $this->stringExpression->dispatch($sqlWalker),
            $this->delimiterExpression->dispatch($sqlWalker),
            $this->positionExpression->dispatch($sqlWalker),
            $this->delimiterExpression->dispatch($sqlWalker)
        );
    }

With sprintf, we prepare the necessary arrangements and place our parsed DQL data's expressions into the SQL code. This turns the following code:

SELECT SPLIT_STR(‘a|bb|ccc|dd’, ‘|’, 3) FROM Product e

Through DQL processing, it becomes:

SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(‘a|bb|ccc|dd’, ‘|’, 3), LENGTH(SUBSTRING_INDEX(‘a|bb|ccc|dd’, ‘|’, 3–1)) + 1), ‘|’, ‘’) AS sclr_0 FROM Products p0_

Finally, in order for DQL to recognize the SPLIT_STR function, we need to open our doctrine.yaml file and make the following adjustments:

I would like to provide an example related to real-life usage alongside what we have done so far.

Normally, you cannot execute the GROUP_CONCAT SQL function in DQL because DQL doesn’t recognize the GROUP_CONCAT function, and you would get an error like the following:

We were able to solve this situation very easily using Custom DQL:

Of course, the specific solution above was evaluated based on our usage of GROUP_CONCAT in the project. This serves as an example of using this Custom DQL:

'GROUP_CONCAT((SELECT c.id FROM App\Entity\xx c WHERE pp.value = c.id AND pp.name = \'xxx\')) as x',
'GROUP_CONCAT((SELECT c2.id FROM App\Entity\yy c2 WHERE pp.value = c2.id AND pp.name = \'yyy\')) as y',

In summary, writing Custom DQL allows us to create SQL functions that are either not supported by DQL or would normally be complex and lengthy. This makes life easier for us by simplifying the process :)