Skip to content

Instantly share code, notes, and snippets.

@jasonhofer
Last active December 30, 2021 21:11
Show Gist options
  • Save jasonhofer/8420677 to your computer and use it in GitHub Desktop.
Save jasonhofer/8420677 to your computer and use it in GitHub Desktop.
Doctrine TYPE() function for DQL. Provides a way to access an entity's discriminator field in DQL queries.
<?php
namespace My\Doctrine\ORM\Query\Functions;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\QueryException;
use Doctrine\ORM\Query\SqlWalker;
/**
* Provides a way to access an entity's discriminator field in DQL
* queries.
*
* Assuming the same "Person" entity from Doctrine's documentation on
* Inheritence Mapping, which has a discriminator field named "discr":
*
* Using the TYPE() function, DQL will interpret this:
*
* <pre>'SELECT TYPE(p) FROM Person p'</pre>
*
* as if you had written this:
*
* <pre>'SELECT p.discr FROM Person p'</pre>
*
* This conversion happens at the SQL level, so the ORM is no longer
* part of the picture at that point.
*
* Normally, if you try to access the discriminator field in a DQL
* Query, Doctrine will complain that the field does not exist on the
* entity. This makes sense from an ORM point-of-view, but having
* access to the discriminator field allows us to, for example:
*
* - get the type when we only have an ID
* - query within a subset of all the available types
*/
class TypeFunction extends FunctionNode
{
/**
* @var string
*/
public $dqlAlias;
/**
* @param SqlWalker $sqlWalker
* @return string
*/
public function getSql(SqlWalker $sqlWalker)
{
$qComp = $sqlWalker->getQueryComponent($this->dqlAlias);
/** @var \Doctrine\ORM\Mapping\ClassMetadataInfo $class */
$class = $qComp['metadata'];
$tableAlias = $sqlWalker->getSQLTableAlias($class->getTableName(), $this->dqlAlias);
if (!isset($class->discriminatorColumn['name'])) {
throw QueryException::semanticalError(
'TYPE() only supports entities with a discriminator column.'
);
}
return $tableAlias . '.' . $class->discriminatorColumn['name'];
}
/**
* @param Parser $parser
*/
public function parse(Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$this->dqlAlias = $parser->IdentificationVariable();
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
}
@webdevilopers
Copy link

Does it work on ORDER BY and GROUP BY too? At least I can use it as an alias and than ORDER / GROUP by that alias?

@jasonhofer
Copy link
Author

I haven't tried it, but my assumption is that it will work, since it is always going to resolve to the same "table.field" no matter where it is in the query.

@dave-redfern
Copy link

Yes, it will work in ORDER BY provided you do: TYPE(p) AS HIDDEN some_name and then ->addOrderBy('some_name', 'ASC')

@validaide-devops
Copy link

Thanks for this snippet! I can confirm that it - still - works on an ORDER BY :)

@CaillaudPA
Copy link

CaillaudPA commented Mar 31, 2020

Thank you very much :)

For doctrine config yml file :

orm:   
    dql:
        string_functions:
            TYPE: App\DQL\TypeFunction

@skirato
Copy link

skirato commented Dec 30, 2021

Works with GROUP BY like so:
$this->entityRepository->createQueryBuilder('e') ->select('TYPE(e) as type') ->groupBy('type') ->getQuery() ->getResult();

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment