Doctrine Join DQL

Question

I's like to do a join between 2 tables on a specific ID. At the moment, I have this DQL:

$q = Doctrine_Query::create()
         ->select('e.*, i.itemName, i.itemtypeId')
         ->from('Model_EventItem e')
         ->leftJoin('Model_Item i ON e.itemId = i.itemId')
         ->where('e.eventitemId = ?', $event->eventId)
         ->orderBy('i.itemName ASC');

The result is empty, although my eventId has a value ... Can you help me please? I there somewhere a tutorial on DQL-joins? I don't get it right with the help of the Doctrine documentation.

Thanks!

PS I have doctrine working in combination with Zend Framework.

Is `$q` empty after you use the `execute()` function on the DQL? Because this query in your question won't do anything with the database. — DrColossos, Jun 21 '10 at 16:37
try `->leftJoin(e.Model_Item i)` the `ON` clause is added by Doctrine accodring to your mapping. For further examples check out http://www.doctrine-project.org/projects/orm/1.2/docs/manual/dql-doctrine-query-language/en#join-syntax — DrColossos, Jun 22 '10 at 08:03

score 1 · Accepted Answer · answered Jun 22 '10 at 19:29

1

you need add a relation to the model and join the tables using the relation

$q = Doctrine_Query::create()
     ->select('e.*, i.itemName, i.itemtypeId')
     ->from('Model_EventItem e')
     ->leftJoin('Model_EventItem.Model_Item i')
     ->where('e.eventitemId = ?', $event->eventId)
     ->orderBy('i.itemName ASC');

answered Jun 22 '10 at 19:29

Andreas Linden

12,489
7
51
67

Ok, But how can I add a relation between models? I have no idea ... Never done it – koenHuybrechts Jun 25 '10 at 09:01
you can define relations in the yaml schema or implement them yourself in the setUp-method of the (Base)Model – Andreas Linden Aug 05 '10 at 20:59

score 1 · Answer 2 · answered Dec 21 '10 at 08:50

you should change the name in the left join from Model_EventItem to e

$q = Doctrine_Query::create()
     ->select('e.*, i.itemName, i.itemtypeId')
     ->from('Model_EventItem e')
     ->leftJoin('Model_EventItem.Model_Item i')
     ->where('e.eventitemId = ?', $event->eventId)
     ->orderBy('i.itemName ASC');

score 0 · Answer 3 · answered Jun 22 '10 at 08:35

0

$q = Doctrine_Query::create()
     ->select('e.*, i.itemName, i.itemtypeId')
     ->from('Model_EventItem e, e.Model_Item i')
     ->where('e.eventitemId = ?', $event->eventId)
     ->orderBy('i.itemName ASC');

answered Jun 22 '10 at 08:35

Tom

30,090
27
90
124

There is something wrong zith my relations in the schema ... This error pops up: Message: Unknown relation alias Model_Item – koenHuybrechts Jun 22 '10 at 12:42
Yep, best to check that. Doctrine models are usually written in camel case rather than with underscores. – Tom Jun 22 '10 at 14:12

Doctrine Join DQL

3 Answers3

Linked