I am trying to get Doctrine2 Entities, ordered by their ID which apparently is a String even though it contains only Numbers. So what I would like to do is something like this:
SELECT entity1, cast (entity1.id AS integer) AS orderId
FROM Namespace\Bla\MyEntity
ORDER BY orderId
Is there a way to do something like this in Doctrine2? Or, what would be the best practise to get my Result if i can't change the type of the id (due to customer requirements of course)?
Attention: I am not asking SQL Code, i am asking for a Doctrine2 Solution, preferably in DQL
You should be able to add your own function to implement this feature.
The class would look something like this:
namespace MyProject\Query;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
class CastAsInteger extends FunctionNode
{
public $stringPrimary;
public function getSql(SqlWalker $sqlWalker)
{
return 'CAST(' . $this->stringPrimary->dispatch($sqlWalker) . ' AS integer)';
}
public function parse(Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$this->stringPrimary = $parser->StringPrimary();
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
}
You'll need to register your function:
$config = $em->getConfiguration();
$config->addCustomNumericFunction('INT', CastAsInteger::class);
Then you can use it:
SELECT e, INT(e.id) AS HIDDEN orderId
FROM Namespace\Bla\MyEntity e
ORDER BY orderId
PS: By adding the HIDDEN
keyword, the alias orderId
won't be in the results (and is only used for ordering).