0

is there any way using DQL write a query that joins table with itself?

j0k
  • 22,600
  • 28
  • 79
  • 90
Ris90
  • 841
  • 2
  • 13
  • 31

1 Answers1

3

Remember, when writing DQL, you're not really writing any SQL yourself, you're talking to your object model at the ORM's level. So when you use 'JOIN' in DQL, you're referencing a relationship between entities, not actual tables.

So I would create a self-referencing relationship in an entity, then use DQL to join that relationship:

$em->createQuery('SELECT a FROM MyEntity a JOIN a.foo f WHERE f.bar = 'somevalue')
Bryan M.
  • 17,142
  • 8
  • 46
  • 60
  • 1
    Thanks for your answer, but in my case I want to write this query : SELECT * FROM table as t1 LEFT JOIN table AS t2 ON t1.field1 = t2.field1 AND t1.field2 = t2.field2 AND t2.date > t1.date WHERE t2.id IS NULL. This query is used to select from list with equal field1 and field2 only one entity with biggest date field. Is there any way to write this query in DQL? – Ris90 Sep 15 '10 at 07:55
  • What type of relationship would you recommend setting up for the entity? Both `OneToMany` and `ManyToOne` (Self-Referencing) save the ids for the referenced entity in a field or a related table when you persist the entity. But that's not going to help with a random join query, because you wouldn't have the data necessarily stored there, e.g. you aren't going to select all the relationships each time you save the entity. See my question: http://stackoverflow.com/questions/27007090/inner-join-results-from-select-statement-using-doctrine-querybuilder – Chadwick Meyer Nov 20 '14 at 01:45