DQL User-Defined Functions

Objective

Doctrine doesn't support MySQL's YEAR() function, as it doesn't translate to the other databases that Doctrine supports.

A dropdown control filters listings by year. The records store the date value as datetime which includes year, month, date, etc.

In MySQL SELECT t.* FROM MyTable t WHERE YEAR(t.create_date) = :year. It's necessary to define this function for Doctrine in order to use it.

What it does

A custom function returns the SQL that can be used by Doctrine to interpret YEAR() in DQL.

This will only work for MySQL, which goes against the intent of using Doctrine.

User-defined function

The user-defined function translates the DQL to native MySQL.

namespace NorthRose\InvoiceBundle\DQL;

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

class Year extends FunctionNode
{
	public $yearExpression;
	
	public function parse(\Doctrine\ORM\Query\Parser $parser )
	{
		$parser->match(Lexer::T_IDENTIFIER);
		$parser->match(Lexer::T_OPEN_PARENTHESIS);
		$this->yearExpression = $parser->ArithmeticPrimary();
		$parser->match(Lexer::T_CLOSE_PARENTHESIS);
	}
	
	public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
	{
		return 'YEAR('.$this->yearExpression->dispatch($sqlWalker).')';
	}
}

Making the routine available to the application

# /app/config/config.yml

# ...

    orm:
        entity_managers:
            default:
                auto_mapping: true
                dql:
                    datetime_functions:
                        YEAR: NorthRose\InvoiceBundle\DQL\Year

Usage

Now YEAR() can be interpreted by DQL:

public function findByCriterial( $criteria ) 
	{
		$qb = $this->createQueryBuilder('i')
			->orderBy('i.date', 'DESC')
			->addOrderBy('i.number', 'DESC')
			->where($qb->expr()->eq('YEAR(i.date)', ':year'))
			->setParameter('year', $criteria['year']->getYear());
		// ...
	}

Notes

• Trac ticket 252: “Add filters to invoice listings”