6

Hi i have this query writen in phpmyadmin and it works gr8.

SELECT u.* FROM users AS u WHERE
        u.id = 14469 OR
        u.id = 685

        ORDER BY u.id, field(u.id, 14469, 685)

But i need to write it in symfony2. How it will looks like? Because this is throwing me an error:

    $query=$this->_em->createQuery("SELECT u FROM UserBundle:User u WHERE
        u.id = 14469 OR
        u.id = 685

        ORDER BY u.id, field(u.id, 14469, 685)
    ");

An exception has been thrown during the rendering of a template ("[Syntax Error] line 0, col 122: Error: Expected end of string, got '('")

Or its not allowed and i have to install and use some doctrine extension?

Lukas Lukac
  • 7,766
  • 10
  • 65
  • 75

2 Answers2

7

The FIELD() function is MySQL specific and therefore not part of the Doctrine 2 library. You can use a custom DQL function, which is already created by the lead developer of Doctrine (Benjamin Eberlei). https://github.com/beberlei/DoctrineExtensions/.

Also I'm pretty sure you need to define the FIELD() function as hidden because you can't use functions in the order by in DQL. Marking it as hidden will prevent the function output to be hydrated in the resultset. Something in the line of:

$query=$this->_em->createQuery("SELECT u, field(u.id, 14469, 685) as HIDDEN field FROM UserBundle:User u WHERE
    u.id = 14469 OR
    u.id = 685
    ORDER BY u.id, field
");
BenMorel
  • 34,448
  • 50
  • 182
  • 322
Bram Gerritsen
  • 7,178
  • 4
  • 35
  • 45
  • Hmm seems complicated. Okay that's what i needed to know (funtion is MySQL specific) – Lukas Lukac Nov 08 '13 at 19:42
  • It's really not that complicated. If you are using composer for example it's just a matter of adding a dependency to the DoctrineExtensions library and adding 1 line of symfony configuration to register the custom DQL function. – Bram Gerritsen Nov 08 '13 at 19:46
  • Hmmm and could you please help me with that? If i would like to install it via composer. I know i should add something like: "gedmo/doctrine-extensions": "master-dev", "stof/doctrine-extensions-bundle": "dev-master", but i am not sure what in this case. I am looking into documentation but there is nothing about installing it via composer or is it? – Lukas Lukac Nov 08 '13 at 20:23
  • You can have a look at the [composer.json](https://github.com/beberlei/DoctrineExtensions/blob/master/composer.json) to find out the package name. it is "beberlei/DoctrineExtensions". So you can add "beberlei/DoctrineExtensions": "dev-master" to your requires, make sure you also put in "minimum-stability": "dev". If you want the tagged (stable) version you can use "beberlei/DoctrineExtensions": "v0.1". – Bram Gerritsen Nov 08 '13 at 20:46
  • I am not sure if i get the minimum stability sentence. But i have put this to the composer.json file: "beberlei/DoctrineExtensions": "dev-master", then run install what throwed an error: Problem 1 - Installation request for symfony/framework-standard-edition 2.1.x-dev -> satisfiable by symfony/framework-standard-edition[2.1.x-dev]. - symfony/framework-standard-edition 2.1.x-dev requires beberlei/doctrineextensions dev-master -> no matching package found. I have symfony2.1.7 – Lukas Lukac Nov 09 '13 at 09:28
0

You can use "index by" to return a result indexed by your entity id. Then you only have to loop over your ordered array, and pick the right entity in the results.

Neekobus
  • 1,870
  • 1
  • 14
  • 18