2

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?

Somy A
  • 1,682
  • 15
  • 18
  • Try moving that last `ON` clause to it's proper place after the `LEFT JOIN` clause. As written it does not look syntactically correct. – BellevueBob Apr 02 '13 at 12:44
  • yes, i'm already try it. but i got different result compared to native query. – Somy A Apr 02 '13 at 12:46
  • according to https://groups.google.com/forum/#!topic/doctrine-user/0rNbXlD0E_8, i think impossible to do this query with Doctrine. – Somy A Apr 03 '13 at 02:54

3 Answers3

1

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).

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • i got no luck, i try your solutions. It works on native SQL, but got no luck on DQL. I got an error: Error: Class '(' is not defined. Exactly after "LEFT JOIN (". I think doctrine join expression doesn't understand about what sub query, so it processed like a class/entity. – Somy A Apr 02 '13 at 13:17
  • 1
    Oh well, sorry. As I said, it was just a guess; I don't know doctrine. You may want to delete the MySQL tag and clarify your question so it's clear your problem is only with doctrine. I'll leave this answer anyway; might help someone else with a real solution. – BellevueBob Apr 02 '13 at 13:43
  • mysql tag removed. Thanks for your help. i appreciate it. :D – Somy A Apr 02 '13 at 17:16
  • I have tried joining the results from a select statement as well, and it doesn't work in DQL. I believe this is because DQL is an **entity** query language, for interacting with entities that are related to each other, and doesn't allow raw queries that use temp tables etc. See my related question: http://stackoverflow.com/questions/27007090/inner-join-results-from-select-statement-using-doctrine-querybuilder – Chadwick Meyer Nov 20 '14 at 17:39
0

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.

UFTimmy
  • 589
  • 3
  • 4
  • Maybe i'm wrong interpreting your answer. But how it possible to query a entity left joined with property its self? Sorry, for late respond. – Somy A Oct 07 '13 at 08:17
0

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

Henry
  • 7,721
  • 2
  • 38
  • 38