Doctrine: Unable to execute either CROSS JOIN or SELECT FROM Table1, Table2?
- by ropstah
Using Doctrine I'm trying to execute either a 1. CROSS JOIN statement or 2. a SELECT FROM Table1, Table2 statement. Both seem to fail.
The CROSS JOIN does execute, however the results are just wrong compared to executing in Navicat.
The multiple table SELECT doesn't event execute because Doctrine automatically tries to LEFT JOIN the second table.
The cross join statement (this runs, however it doesn't include the joined records where the refClass User_Setting doesn't have a value):
$q = new Doctrine_RawSql();
$q->select('{s.*}, {us.*}')
->from('User u
CROSS JOIN Setting s
LEFT JOIN User_Setting us
ON us.usr_auto_key = u.usr_auto_key
AND us.set_auto_key = s.set_auto_key')
->addComponent('u', 'User u')
->addComponent('s', 'Setting s')
->addComponent('us', 'u.User_Setting us')
->where('s.sct_auto_key = ? AND u.usr_auto_key = ?',array(1, $this->usr_auto_key));
And the select from multiple tables (this doesn't event run. It does not spot the many-many relationship between User and Setting in the first ->from() part and throws an exception: "User_Setting" with an alias of "us" in your query does not reference the parent component it is related to.):
$q = new Doctrine_RawSql();
$q->select('{s.*}, {us.*}')
->from('User u, Setting s
LEFT JOIN User_Setting us
ON us.usr_auto_key = u.usr_auto_key
AND us.set_auto_key = s.set_auto_key')
->addComponent('u', 'User u')
->addComponent('s', 'Setting s')
->addComponent('us', 'u.User_Setting us')
->where('s.sct_auto_key = ? AND u.usr_auto_key = ?',array(1, $this->usr_auto_key));
