mysql - Doctrine : Which syntax for GROUP_CONCAT with a CASE WHEN?

Question

我有这个 SQL 查询，它在 phpMyAdmin 中运行良好:

SELECT `Tag_Type_Code`, `Tag_Type`,
    GROUP_CONCAT(`Tag_Code` ORDER BY `Tag_Name`) as tagCode,
    GROUP_CONCAT(`Tag_Name` ORDER BY `Tag_Name`) as tagName,
    GROUP_CONCAT(CASE WHEN isnull(`Tag_Picto`) THEN 'NULL' ELSE `Tag_Picto` END ORDER BY `Tag_Name`) as tagPicto
FROM tags
WHERE `modele_code` = "MYCODE"
GROUP BY `Tag_Type_Code`
ORDER BY `tag_Type` ASC

我想在 Doctrine 和 Symfony3 中执行此操作，我尝试了此操作，但在 CASE WHEN 附近出现语法错误。

$this->getEntityManager()->createQuery(
    'SELECT tag.typeCode, tag.type,
        GROUP_CONCAT(tag.code ORDER BY tag.name) as tagCode,
        GROUP_CONCAT(tag.name ORDER BY tag.name) as tagName,
        GROUP_CONCAT(CASE WHEN tag.picto IS NULL THEN "NULL" ELSE tag.picto END ORDER BY tag.name) as tagPicto
    FROM AppBundle:Tag tag
    WHERE tag.modelCode = :code
    GROUP BY tag.typeCode
    ORDER BY tag.type ASC'
)->setParameter('code', $modelCode)
->getResult();

或者这个:

'SELECT tag.typeCode, tag.type,
    CASE WHEN tag.picto IS NULL THEN "NULL" ELSE tag.picto END as picto
    GROUP_CONCAT(tag.code ORDER BY tag.name) as tagCode,
    GROUP_CONCAT(tag.name ORDER BY tag.name) as tagName,
    GROUP_CONCAT(picto ORDER BY tag.name) as tagPicto
FROM AppBundle:Tag tag
WHERE tag.modelCode = :code
GROUP BY tag.typeCode
ORDER BY tag.type ASC'

我还尝试使用 beberlei/DoctrineExtensions bundle 中的 IfElse 和 IfNull 扩展但我找不到正确的语法。

此查询无需 CASE WHEN 即可运行。

感谢您的帮助。

Answer 1

我认为你的问题是在 "NULL" 处使用双引号，使用单引号并转义它们:

$this->getEntityManager()->createQuery(
    'SELECT tag.typeCode, tag.type,
        GROUP_CONCAT(tag.code ORDER BY tag.name) as tagCode,
        GROUP_CONCAT(tag.name ORDER BY tag.name) as tagName,
        GROUP_CONCAT(CASE WHEN tag.picto IS NULL THEN \'NULL\' ELSE tag.picto END ORDER BY tag.name) as tagPicto
    FROM AppBundle:Tag tag
    WHERE tag.modelCode = :code
    GROUP BY tag.typeCode
    ORDER BY tag.type ASC'
)->setParameter('code', $modelCode)
->getResult();