title: yii CDbCriteria Join Column not found
body: <p>I've been struggling with converting the following SQL to CDBCriteria to be used with a CActiveDataProvider:
  "SELECT PresetDeviceLink.<em>, Device.</em> FROM PresetDeviceLink INNER JOIN Device ON Device.id = PresetDeviceLink.deviceId WHERE Device.roomId = 1"</p>
  
  <p>Table structure is as follows:</p>
  
  <pre><code>mysql&gt; describe PresetDeviceLink;
  +----------+---------+------+-----+---------+----------------+
  | Field    | Type    | Null | Key | Default | Extra          |
  +----------+---------+------+-----+---------+----------------+
  | id       | int(11) | NO   | PRI | NULL    | auto_increment |
  | presetId | int(11) | NO   |     | NULL    |                |
  | deviceId | int(11) | NO   |     | NULL    |                |
  | state    | int(11) | NO   |     | 0       |                |
  | value    | int(11) | NO   |     | 32      |                |
  +----------+---------+------+-----+---------+----------------+
  
  mysql&gt; describe Device;
  +-------------+--------------+------+-----+---------+----------------+
  | Field       | Type         | Null | Key | Default | Extra          |
  +-------------+--------------+------+-----+---------+----------------+
  | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
  | ref         | int(11)      | NO   |     | NULL    |                |
  | roomId      | int(11)      | NO   |     | NULL    |                |
  | typeId      | int(11)      | NO   |     | NULL    |                |
  | paired      | tinyint(1)   | NO   |     | 0       |                |
  | name        | varchar(255) | YES  |     | NULL    |                |
  | description | text         | YES  |     | NULL    |                |
  | dimmerPos   | int(11)      | NO   |     | 0       |                |
  +-------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>My code in my controller is as follows:</p>
  
  <pre><code>$criteria = new CDbCriteria;
  $criteria-&gt;select = 'PresetDeviceLink.*, Device.*';
  $criteria-&gt;join = 'INNER JOIN Device ON Device.id = PresetDeviceLink.deviceId';
  $criteria-&gt;condition = 'Device.roomId = 1';
  
  $presetDeviceLink=new CActiveDataProvider('PresetDeviceLink', array(
      'criteria' =&gt; $criteria,
  ));
  </code></pre>
  
  <p>When run I get the following error:</p>
  
  <pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: &lt;b&gt;Column not
  found&lt;/b&gt;: 1054 Unknown column 'PresetDeviceLink.deviceId' in 'on clause'. The SQL
  statement executed was: SELECT COUNT(*) FROM `PresetDeviceLink` `t` INNER JOIN
  Device ON Device.id = PresetDeviceLink.deviceId WHERE Device.roomId = 1 
  </code></pre>
  
  <p>The strange thing is, If I use 'Device' as the CActiveDataProvider source and change the join statement to join to 'PresetDeviceLink', it then complains that the Device.roomId column could not be found.</p>
  
  <p>Do I just not understand how CActiveDataProvider works?  It looks to me that I can only use a condition (in a join or where clause) from a field in the table that I pass to the CActiveDataProvider. Any advice?</p>
  
  <p>PS - the SQL query works beautifully in the MySQL console.</p>
  
  <p>Thanks in advance,
  Ben</p>