Doctrine 2 mysql FIELD function in order by

Question

I'm trying to use the MySQL FIELD function in an order by clause in a query. I'm assuming that Doctrine 2 doesn't support the FIELD function out of the box - is that true? If so, how can I use it? Will I have to turn my whole query into a native query? Is there a Doctrine 2 extension that adds this functionality?

Solution 1

Jeremy Hicks, thanks for your extension. I didn`t know how to connect your function to doctrine, but finally i find answer.

$doctrineConfig = $this->em->getConfiguration();
$doctrineConfig->addCustomStringFunction('FIELD', 'DoctrineExtensions\Query\Mysql\Field');

I need FIELD function to order my Entities that i select by IN expression. But you can use this function only in SELECT, WHERE, BETWEEN clause, not in ORDER BY.

Solution:

$qb
            ->select("r, field(r.id, " . implode(", ", $ids) . ") as HIDDEN field")
            ->from("Entities\Round", "r")
            ->where($qb->expr()->in("r.id", $ids))
            ->orderBy("field");

To avoid adding field alias into your result row you need put HIDDEN keyword. So this how to be able order values in IN expression in Doctrine 2.2.

Solution 2

You could add support for the FIELD() DQL function but instead implement it as standard SQL CASE .. WHEN expression. This way your function would work both on MySQL and Sqlite, which is particularly useful if you are like me and like to run your unit tests on in-memory sqlite.

This class is largely based on the work by Jeremy Hicks (I simply changed the getSql() method)

class Field extends FunctionNode
{
    private $field = null;
    private $values = array();

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        // Do the field.
        $this->field = $parser->ArithmeticPrimary();

        // Add the strings to the values array. FIELD must
        // be used with at least 1 string not including the field.

        $lexer = $parser->getLexer();

        while (count($this->values) < 1 ||
                $lexer->lookahead['type'] != Lexer::T_CLOSE_PARENTHESIS) {
            $parser->match(Lexer::T_COMMA);
            $this->values[] = $parser->ArithmeticPrimary();
        }

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

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        $query = '(CASE ' . $this->field->dispatch($sqlWalker);
        for ($i=0, $limiti=count($this->values); $i < $limiti; $i++) {
            $query .= ' WHEN ' . $this->values[$i]->dispatch($sqlWalker) . ' THEN     ' . $i;
        }
            $query .= ' END)';

        return $query;
    }
}

Solution 3

You can write your own DQL extension.

Comments

[Jeremy Hicks]

I attempted to do just that: pastebin.com/Zg7ggQxV The part I'm unsure of is the EBNF tokens. doctrine-project.org/docs/orm/2.0/en/reference/… Anybody care to take a look and see if I've done it correctly? I just used ArithmeticPrimary although it looked like I maybe was supposed to use IdentificationVariable and/or FieldIdentificationVariable for the column value. I couldn't even find a function for FieldIdentificationVariable() even though it's listed in the EBNF grammar.

[gphilip]

Thank you for this, it worked! Here is an easier way to register the function by the way: symfony.com/doc/2.0/cookbook/doctrine/custom_dql_functions.h‌​tml Be careful that the config key has to be the function name

[Mr Hash]

You can also simply using parameters such as: ->select("r, field(r.id, :ids) as HIDDEN field")->from("Entities\Round", "r")->where("r.id IN :ids")->setParameter('ids', $ids)->orderBy("field");

[mazatwork]

Good answer +1. But your CASE() doesn't exactly behave like Mysql's FIELD(). To fix this, use $query .= ' WHEN ' . $this->values[$i]->dispatch($sqlWalker) . ' THEN ' . ($i + 1); and $query .= ' ELSE 0 END)';

[Brandon Dusseau]

If you're from the future like me, note that the linked extension has moved here.