mysql - 案例在 Doctrine 查询生成器 where 子句中不起作用

Question

我在where子句中遇到mysql CASE问题，

 $rides_q1 = $em->getRepository('IRRideBundle:Ride');
    $query = $rides_q1->createQueryBuilder('a')
                     ->where('(CASE WHEN a.ephemeral = 1 THEN a.date_beginning < NOW() AND a.date_ending > NOW()ELSE a.ephemeral = 0 END) AND a.active = 1 AND a.status = 5')                  
     ->getQuery();
$ridesQuery = $query->execute();



**[Syntax Error] line 0, col 93: Error: Expected Doctrine\ORM\Query\Lexer::T_ELSE, got '<'**

[2/2] QueryException: [Syntax Error] line 0, col 93: Error: Expected Doctrine\ORM\Query\Lexer::T_ELSE, got '<'   +
[1/2] QueryException: SELECT a FROM IR\RideBundle\Entity\Ride a WHERE (CASE WHEN ephemeral = 1 THEN date_beginning < NOW() AND date_ending > NOW()ELSE ephemeral = 0 END) 

我想准备这个sql查询，它与phpmyadmin一起工作正常

SELECT * FROM ride WHERE (CASE WHEN ephemeral = 1 THEN date_beginning < NOW() AND date_ending > NOW()ELSE ephemeral = 0 END) AND active = 1 AND `status_id` = 5 ORDER BY `date_ending` DESC

Answer 1

WHERE 中的 CASE 表达式是错误的。您可以将其转换为正常的复合条件，例如

(CASE WHEN a.ephemeral = 1 THEN a.date_beginning < NOW() AND a.date_ending > NOW()ELSE a.ephemeral = 0 END)

至

SELECT * FROM ride
WHERE ((a.ephemeral = 1 
       and a.date_beginning < NOW() 
       AND a.date_ending > NOW()) 
  OR a.ephemeral = 0)
  AND a.active = 1 
  AND a.status = 5
ORDER BY `date_ending` DESC