教义FIND_IN_SET导致错误:预期的字符串结尾，得到了'(' [英] Doctrine FIND_IN_SET leads to Error: Expected end of string, got '('

问题描述

我在Symfony2中有以下学说查询:

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)
;

但这会导致以下错误:

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

和:

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

我使用了FIND_IN_SET 来自beberlei的学说扩展，以便能够在查询中使用它.

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

为什么会发生这种情况?

Any ideas why this happens?

更新:

通过以下方式在SELECT中将FIND_IN_SET用作别名:

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

导致以下错误:

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

推荐答案

感谢@qooplmao的评论，这是有效的版本:

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只有两个参数，并且必须在查询的SELECT部分中，因为函数不能在查询的ORDER BY部分中使用.

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.

这篇关于教义FIND_IN_SET导致错误:预期的字符串结尾，得到了'('的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！