Doctrine: Unable to execute either CROSS JOIN or SELECT FROM Table1, Table2?

Posted by ropstah on Stack Overflow See other posts from Stack Overflow or by ropstah
Published on 2010-04-17T23:05:01Z Indexed on 2010/04/17 23:13 UTC
Read the original article Hit count: 216

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.

  1. The CROSS JOIN does execute, however the results are just wrong compared to executing in Navicat.
  2. The multiple table SELECT doesn't event execute because Doctrine automatically tries to LEFT JOIN the second table.

    1. 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));
      
    2. 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));
      

© Stack Overflow or respective owner

Related posts about php

Related posts about doctrine