Doctrine FIND_IN_SET leads to Error: Expected end of string, got '('

Question

I've got the following doctrine query in Symfony2:

$query = $em
    ->createQuery("
        SELECT m FROM MyBackendBundle:Merchant m
        WHERE m.active = :active
        ORDER BY FIND_IN_SET(m.range, 'all', 'without_special'), m.tradingAmount DESC
    ")
    ->setParameter('active', true)
;

But this leads to the following error:

[Syntax Error] line 0, col 112: Error: Expected end of string, got '('

and:

QueryException: 
SELECT m FROM My\Bundle\BackendBundle\Entity\Merchant m 
WHERE m.active = :active 
ORDER BY FIND_IN_SET(m.range, 'all', 'without_special') ASC, m.tradingAmount DESC

I use the FIND_IN_SET doctrine extension from beberlei to be able to use it in the query.

Any ideas why this happens?

Update:

Using the FIND_IN_SET in the SELECT as an alias this way:

SELECT m, FIND_IN_SET(m.range, 'all', 'without_special') AS HIDDEN findInSet
FROM MyBackendBundle:Merchant 
WHERE m.active = :active
ORDER BY findInSet, m.productAmount DESC

results in the following error:

[Syntax Error] line 0, col 56: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','

I'm not sure but i don't think you can use functions in the `order_by`. One way would be to create an alias for that result like `SELECT m, FIND_IN_SET(m.range, 'all', 'without_special') as HIDDEN findInSet FROM ....` and then use that alias in the `order_by` instead. — qooplmao, Mar 27 '16 at 11:02
Tried that, results in: `QueryException: [Syntax Error] line 0, col 53: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','` Which doesn't make that much sense to me, because there is a closing parenthesis, but no comma. — Christian Kolb, Mar 27 '16 at 12:50
Sorry, I'm an idiot. It doesn't look like `FIND_IN_SET` takes 3 arguments, either in the extensions pack or in MySQL in general. If `all` and `without_special` are supposed to be the set then I think they are supposed to be in 1 arguments like `FIND_IN_SET(m.range, 'all,without_special')`. — qooplmao, Mar 27 '16 at 19:41

score 1 · Accepted Answer · answered Mar 27 '16 at 20:33

Thanks to the comments of @qooplmao this is the working version:

$query = $em
    ->createQuery("
        SELECT m, FIND_IN_SET(m.range, 'all,without_special') AS rangeOrdering
        FROM MyBackendBundle:Merchant m
        WHERE m.active = :active
        ORDER BY rangeOrdering, m.tradingAmount DESC
    ")
    ->setParameter('active', true)
;

FIND_IN_SET has only two parameters and has to be within the SELECT part of the query as functions can't be used in the ORDER BY part of the query.

Doctrine FIND_IN_SET leads to Error: Expected end of string, got '('

1 Answers1

Linked