CODEWITHSUNDEEP
doctrine-ormdoctrinequery-builder
Somy A
Somy A

Reputation: 1712

Is it possible to convert into DQL or Doctrine Query Builder

I have 3 tables with column

A:
    id
B:
    id
    a_id
C:
    id
    b.id

with native query:

SELECT a.id, b.id, c.id
FROM A as a 
LEFT JOIN B as b 
    INNER JOIN C as c ON b.id = c.b_id
ON a.id = b.a_id

i have tried

SELECT a.id, b.id, c.id 
    FROM App\HomeBundle\Entity\A as a
    LEFT JOIN App\HomeBundle\Entity\B as b
        INNER JOIN App\HomeBundle\Entity\C as c
        ON c.id = c.bId
    ON a.id = b.aId

i got error:

Error: Expected Literal, got 'JOIN'

Is it possible to convert my native query to DQL or query builder? If possible, how it will be look like?

Upvotes: 2

Views: 1162

Answers (3)

Henry
Henry

Reputation: 7891

I've always had to use the WITH keyword instead of ON when using doctrine.

Upvotes: 0

UFTimmy
UFTimmy

Reputation: 599

This is an older question, but I believe you need to JOIN on the relationship, and not the entity.

For example, instead of 

SELECT a
    FROM Entity\A as a
    LEFT JOIN Entity\B as b

It should be:

SELECT a
    FROM Entity\A as a
    LEFT JOIN a.entityB as b

If that is not clear, a.entityB is a property of the A entity, called "entityB". That property defines the relationship between A and B.

When you just JOIN entity to entity, Doctrine does not know how they are related. However, if you JOIN based on the property, Doctrine can use the annotations (or other mapping) to determine the relationship of A and B.

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

This is only a guess but too long for a comment. If I'm completely off base I'll delete this answer.

Assuming your native query is syntactically correct, perhaps MySQL is applying the last ON condition to the result of an INNER JOIN between b and c. If that's true, see if this gives you the same result:

SELECT a.id, b.id, c.id 
FROM App\HomeBundle\Entity\A as a
LEFT JOIN (
    SELECT bx.aID
    FROM   App\HomeBundle\Entity\B as bx
    INNER JOIN App\HomeBundle\Entity\C as c
    ON    bx.id = c.bId
    ) b
ON a.id = b.aId

Note I corrected what I believe to be an error in your attempted solution (where you said ON c.id = c.bId).

Upvotes: 1

Related Questions