Doctrine query SQL to DQL

1
votes

I have a select with SQL which is working perfectly, but I cannot translate this query to DQL with createQueryBuilder().

Here's my query:

SELECT distinct c.name_id
FROM cultures AS c
LEFT JOIN ilots AS i ON i.exploitation_id = 1

My current code:

return $this->createQueryBuilder('c')
        ->leftJoin(Ilots::class, 'i', 'ON', 'i.exploitation = 1')
        ->distinct()
        ->getQuery()
        ->getResult();

And the error:

[Syntax Error] line 0, col 74: Error: Expected end of string, got 'ON'

2
phpmysqldoctrine
is there any relationship between Cultures and Ilots ? – Theva
Yeah he have on Cultures ilot_id to id of ilot Entity – Pink3vil

2 Answers

1
votes

In DQL ON doesn't exist, you have to use WITH instead.

return $this->createQueryBuilder('c')
        ->leftJoin(Ilots::class, 'i', 'WITH', 'i.exploitation = 1')
        ->distinct()
        ->getQuery()
        ->getResult();

Documentation

-1
votes

If there is relationship between the Entity:

$qb = $this->entityManager->createQueryBuilder('c')
        ->select('c')
        ->distinct()
        ->from('cultures', 'c')
        ->leftJoin('c.ilots', 'i')
        ->where('i.exploitation = 1')
        ;

    return $qb->getQuery()->getResult();