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?

Bludge

Answer 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.

Barina

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.html Be careful that the config key has to be the function name – Kirsti

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"); – Stingy

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

Answer 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;
    }
}

Nephogram

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)'; – Aggappera

Answer 3

You can write your own DQL extension.

Kainite

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. – Bludge

Answer 4

In case that the field that you want to "order by" is an ENUM data type, then ORDER BY will work in the order in which the values were defined for that ENUM field.

For example, I had a filed defined as enum('n','pe','o','ap','c') that was giving a weird ordering. The ordering got fixed after updating the enum to: enum('ap','c','n','o','pe')

Drumhead