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 comparing to execution in Navicat.
The multiple table SELECT doesn't even 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
andSetting
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));