这段DQL语句是否正确？查询用户即将发生事件报错咨询

Let’s break down the syntax and logical problems in your current code that are triggering the exception, then fix them step by step:

• Invalid semicolon in the SELECT clause: You included a semicolon inside the string passed to select() (END) upComingEvent);), which breaks the query syntax. Semicolons should only end PHP statements, not be part of the DQL string itself.
• Missing AS for alias assignment: While some parsers allow omitting AS, explicitly using AS upComingEvent makes the query clearer and avoids potential parsing glitches in Doctrine.
• Incorrect method name for left join: Doctrine’s Query Builder uses camelCase for methods—so it’s leftJoin() (capital J), not leftjoin() (lowercase j). Using the lowercase version will throw a method-not-found error.
• Null values for non-upcoming events: Your original CASE statement returns null when an event isn’t upcoming, meaning users with no upcoming events will get a null value instead of 0. Adding an ELSE 0 clause fixes this to return a clean numeric value.

Corrected Query Builder Code #

$query = $this->em->createQueryBuilder();
$query->select('COUNT(CASE WHEN e.startDate >= CURRENT_DATE() THEN es.event END) AS upComingEvent')
      ->from('Entities\ES', 'es')
      ->leftJoin('Entities\E', 'e', 'WITH', 'e.id = es.event')
      ->groupBy('es.userId')
      ->orderBy('upComingEvent', 'DESC');

Alternative Efficient Approach #

You can also filter upcoming events directly at the join level, which might perform better for large datasets:

$query = $this->em->createQueryBuilder();
$query->select('COUNT(es.event) AS upComingEvent')
      ->from('Entities\ES', 'es')
      ->leftJoin('Entities\E', 'e', 'WITH', 'e.id = es.event AND e.startDate >= CURRENT_DATE()')
      ->groupBy('es.userId')
      ->orderBy('upComingEvent', 'DESC');

Why the Original Code Failed #

The immediate cause of the exception was the invalid semicolon in the SELECT clause, which broke Doctrine’s query parser. Fixing that alone would resolve the syntax error, but addressing the other issues ensures the query behaves exactly as intended (returning 0 for users with no upcoming events, avoiding method errors, etc.).

内容的提问来源于stack exchange，提问作者Aman Singh