复合表达的学说顺序

Question

我想复制以下查询，该查询按名称对所有用户进行排序，但名称以 Z 开头的用户排在第一位：

SELECT *
FROM user
ORDER BY LEFT(name, 1) != 'Z', name

我有这个：

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('u')
->addSelect($qb->expr()->neq($qb->expr()->substring('u.name', 1, 1), $qb->expr()->literal('Z')) . ' AS HIDDEN firstLetterIsNotZ')
->from(user::class, 'u')
->orderBy('firstLetterIsNotZ')
->addOrderBy('u.name');

生成的 DQL 看起来足够理智：

SELECT s, SUBSTRING(u.name, 1, 1) <> 'Z' AS HIDDEN descriptionFirstLetter
FROM user u
ORDER BY firstLetterIsNotZASC, s.description ASC

但是词法分析器不喜欢它：Error: Expected DoctrineORMQueryLexer::T_FROM, got '<'

Answer 1

Doctrine 不能很好地处理 select 语句中的条件。

您可以通过用CASE WHEN包裹您的条件来产生您想要的结果来规避这个问题。

我还参数化了字母条件，以使其更具可移植性，以防您希望将其添加为动态值。

$em = $this->getEntityManager();
$expr = $em->getExpressionBuilder();
$q = $em->createQueryBuilder()
->select('u')
->addSelect('(CASE WHEN ' . $expr->neq($expr->substring('u.name', 1, 1), ':letter') . ' THEN 0 ELSE 1 END) AS HIDDEN firstLetterIsNotZ')
->from(user::class, 'u')
->orderBy('firstLetterIsNotZ')
->addOrderBy('u.name')
->setParameter('letter', 'Z')
->getQuery();
dump($q->getDQL());
dump($q->getResult());

生成的 DQL

SELECT u, (CASE WHEN SUBSTRING(u.name, 1, 1) <> :letter THEN 1 ELSE 0 END) AS HIDDEN firstLetterIsNotZ 
FROM user u 
ORDER BY firstLetterIsNotZ ASC, u.name ASC

结果数据：(firstLetterIsNotZ not HIDDEN(

array:3 [▼
0 => array:2 [▼
0 => User {#2163 ▶}
"firstLetterIsNotZ" => "0"
]
1 => array:2 [▼
0 => User {#2167 ▶}
"firstLetterIsNotZ" => "1"
]
2 => array:2 [▼
0 => User {#2168 ▶}
"firstLetterIsNotZ" => "1"
]
]