Customizing Doctrine Queries with DQL Functions

Have you ever wanted to perform calculations directly within your Doctrine queries? Doctrine’s Query Language (DQL) offers built-in functions for common tasks, but what if you need something specific? This article will show you how to create your own custom DQL functions to extend Doctrine’s capabilities.

The Power of Custom DQL Functions

Imagine you have a subscriber entity with a dateOfBirth field. In your controller, you want to retrieve all subscribers and display their age alongside their email and name. While you could calculate the age in your controller logic, wouldn't it be cleaner to do it directly in the DQL query?

This is where custom DQL functions come in. They allow you to define your own logic and integrate it seamlessly into your DQL queries.

Building a Custom Age Calculator Function

Let’s break down the provided example which creates a custom function named AGE:

1. Configuration (doctrine.yaml):

# doctrine.yaml
orm:
    dql:
        datetime_functions:
            AGE: App\Doctrine\DQL\AgeCalculator

This part tells Doctrine where to find the implementation for the AGE function. It points to the App\Doctrine\DQL\AgeCalculator class.

2. AgeCalculator Class:

<?php

namespace App\Doctrine\DQL;

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

class AgeCalculator extends FunctionNode
{
    public Node|null $firstDate = null;
    public Node|null $secondDate = null;

    /**
     * @throws QueryException
     */
    public function parse(Parser $parser): void
    {
        $parser->match(TokenType::T_IDENTIFIER);
        $parser->match(TokenType::T_OPEN_PARENTHESIS);
        $this->firstDate = $parser->ArithmeticPrimary();
        $parser->match(TokenType::T_COMMA);
        $this->secondDate = $parser->ArithmeticPrimary();
        $parser->match(TokenType::T_CLOSE_PARENTHESIS);
    }

    /**
     * @throws ASTException
     */
    public function getSql(SqlWalker $sqlWalker): string
    {
        return 'AGE(' .
            $this->firstDate->dispatch($sqlWalker) . ', ' .
            $this->secondDate->dispatch($sqlWalker) .
            ')';
    }
}

This class defines the logic for calculating the age difference.

• It parses the arguments passed to the AGE function (birth date and current date) and builds the appropriate SQL expression.

3. Using the Function in a Repository:

// SubscriberRepository.php
public function getAllSubscriber()
{
    return $this->createQueryBuilder('s')
        ->addSelect('s.email,s.firstName,AGE(CURRENT_DATE() , s.dateOfBirth) AS age')
        ->getQuery()->getResult();
}

The SubscriberRepository demonstrates how to use the AGE function in a DQL query.

• The getAllSubscriber method utilizes the AGE function to calculate the age difference between the current date (CURRENT_DATE()) and the subscriber's dateOfBirth.
• The calculated age is then added as an alias (AS age) to the select clause.

4. Controller Integration:

// Controller.php
#[Route('/all/subscribers', name: 'subscribers', methods: ['GET'])]
public function allSubscribers(SubscriberRepository $repository):Response
{
    $subscribers = $repository->getAllSubscriber();

    return $this->render('subscriber.html.twig', ['subscribers' => $subscribers]);
}

The allSubscribers method in the controller retrieves all subscribers using the repository's getAllSubscriber method.

• The retrieved data (including the calculated age) is then passed to the Twig template for rendering.

Benefits and Considerations

Custom DQL functions offer several advantages:

• Cleaner and more concise queries.
• Reusable logic within your DQL statements.
• Improved separation of concerns between data retrieval and processing.

RESULT

This page displays a list of all subscribers along with their email address, first name, and calculated age.

In Conclusion

Custom DQL functions empower you to extend Doctrine’s capabilities and create more expressive and powerful queries. By following these steps, you can create your own custom DQL functions to streamline your data retrieval tasks and enhance your application logic.