[DC-627] Work on link-table and leftJoin Created: 13/Apr/10  Updated: 13/Apr/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.2
Fix Version/s: None

Type: Bug Priority: Blocker
Reporter: Thomas Tourlourat - Armetiz Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows Seven - WAMP 2.0i


Attachments: PNG File composition.png    

 Description   

Looking at the attachment to see the little schema.
Tree table : items, items_children and children.

The link-table items_children is here to create a many-to-many relationships between Items and Children.

So, after configure the YAML and generated Models.
We can do something like that :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children");
$items = $query->execute ();

$items //An array of items
$items[0]->title //A string
$items[0]->children //An array of children

The SQL Output of the query is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`

All array are order by something, in this case it's a natural order which come from the items_children order into database.

Now, I want to apply a condition, or a filter on the items_children table.
If I translate my demand to SQL Query :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` ORDER BY `c1`.`index`

Or something crazy :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` WHERE `c1`.`index` > 2

But how can I do that with DQL ? Because I have no access to the Link-table.

Link-Table are use to create many-to-many, that could be OOP array. So we have to be allowed to add "index" to order the result. Also, we have to be allowed to add condition on this tables, like "datePromote" or simply "enabled".

The solution should be on the following DQL Query :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children")
->leftJoin ("ItemsChildren link_table")
->orderBy ("link_table.index")
$items = $query->execute ();

But the SQL Output is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`, `items_children` `i3` ORDER BY `i3`.`index`

To conclude, it's a blocker problem. Because I can't use a standard DB schema.

Regards,
Armetiz.


Generated at Fri Apr 18 13:31:37 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.