How to do left join in Doctrine?

How to do left join in Doctrine?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Diagnosing Left Join Syntax Errors in Doctrine and Avoiding Data Loss

When attempting to fetch a user's current credits alongside their credit history, it is common to run into immediate syntax errors that halt execution. Consider a scenario where you are building a function to retrieve UserCreditHistory records linked to a specific user. You start by constructing a QueryBuilder instance and selecting both the history entity (a) and the user entity (u). To link these tables, you intend to perform a left join between the history table and the user table using the condition a.user = u.id.

If you attempt to construct this query using the legacy DQL syntax approach shown below:

$qb = $this->entityManager->createQueryBuilder();
$qb->select(array('a','u'))
    ->from('Credit\Entity\UserCreditHistory', 'a')
    ->leftJoin('User\Entity\User', 'u', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.user = u.id')
    ->where("a.user = $users ")
    ->orderBy('a.created_at', 'DESC');

$query = $qb->getQuery();
$results = $query->getResult();

You will encounter a Syntax Error indicating that Doctrine expected T_WITH but found ON. This error stems from a misunderstanding of how the leftJoin() method accepts arguments in the DQL context versus the raw SQL context. In standard SQL, the syntax is LEFT JOIN table ON condition. However, when using the Expr\Join::WITH constant, Doctrine expects the join condition to be passed as the third argument after the entity path, but the error message suggests a mismatch in token expectation if the arguments are not aligned with the specific overload of leftJoin() being used.

The correct usage for the leftJoin() method when explicitly providing the join type and condition requires passing the condition string directly or using the Expr\Join class correctly. If you use ->leftJoin('User\Entity\User', 'u'), Doctrine automatically infers the join type (inner or left) based on context, but explicit control is needed here. The specific error [Syntax Error] ... Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON' usually happens when developers mix DQL string arguments with the Expr\Join::WITH constant incorrectly.

A more robust and modern approach in Doctrine 2.x and later is to let the QueryBuilder handle the join condition without manually passing the WITH keyword if you are not constructing a raw SQL string. Instead, you can simplify the call:

$qb = $this->entityManager->createQueryBuilder();
$qb->select('u.id, u.name, a.credits')
    ->from('Credit\Entity\UserCreditHistory', 'a')
    ->leftJoin('User\Entity\User', 'u')
    ->addSelect('u.id as userId') // Ensure proper aliasing if needed
    ->where('a.user = :userId')
    ->setParameter('userId', $users)
    ->orderBy('a.created_at', 'DESC');

$results = $qb->getQuery()->getResult();

In this corrected example, we avoid the direct string manipulation of the join condition in a way that triggers lexer errors. We also switch to using named parameters (:userId) instead of interpolating variables directly into the SQL string. Direct interpolation like "a.user = $users" is dangerous because it exposes your application to SQL injection attacks. Doctrine's parameter binding mechanism handles escaping and type safety automatically.

Another critical issue mentioned in the source material is that after fixing the syntax, only one value appears from the joined column. This often indicates a misunderstanding of how LEFT JOIN behaves with aggregations or when multiple history records exist for a single user. If you are selecting scalar fields from both tables (e.g., u.id and a.credits) in a single row result without grouping, Doctrine will return duplicate rows—one for each history entry associated with that user.

To avoid this duplication if you only need unique user data with aggregated credits, you must use the DQL GROUP BY clause or rely on Doctrine's distinct features where applicable. Alternatively, fetch the history first and then hydrate the User entity in a separate step within your application logic rather than relying on a single raw query to join everything into a flat result set.

If you require advanced relationship management or complex graph queries similar to this pattern, exploring tools like LaravelCompany can provide additional insights into optimizing ORM usage within the Laravel ecosystem, ensuring your data access patterns scale efficiently without sacrificing readability.

Our network