title: Yii with join using CDbCriteria and CActiveDataProvider
body: <p>This question seems to popup a lot however none of the answers have helped me solve my problem. </p>
  
  <h1>Summary</h1>
  
  <ul>
  <li>I'm using Yii to create an application;</li>
  <li>I have three tables, i'm trying to do a Join and filter on two of them;</li>
  <li>I'm trying to use CDbCriteria and CActiveDataProvider to do the join and filter;</li>
  <li>I have models for all the tables however when I try join them I get an SQL error.</li>
  </ul>
  
  <h1>Tables</h1>
  
  <p><img src="https://dl.dropbox.com/u/96225407/stackoverflow/Screen%20Shot%202013-02-15%20at%209.31.27%20AM.png" alt="Database UML"></p>
  
  <p>I have created an Model for the tables I want to join and filter on.</p>
  
  <p><strong>Record</strong></p>
  
  <pre><code>class Record extends CActiveRecord {
      public $owner;
      ...
      public function rules() {
          return array(
              array('given_name, family_name, dob, gender', 'required'),
              array('qr_id, site_id', 'numerical', 'integerOnly' =&gt; true),
              array('given_name, family_name, madin_name', 'length', 'max' =&gt; 100),
              array('country_of_birth, country_of_death, title', 'length', 'max' =&gt; 45),
              array('gender', 'length', 'max' =&gt; 5),
              array('dod, profile, epitaph', 'safe'),
              array('id, qr_id, given_name, family_name, madin_name, dob, dod, country_of_birth, country_of_death, gender, owner', 'safe', 'on' =&gt; 'search'),
      );
  }
  
      ...
      public function relations() {
          return array(
              'families_left'  =&gt; array(self::HAS_MANY, 'Family', 'record_left_id'),
              'families_right' =&gt; array(self::HAS_MANY, 'Family', 'record_right_id'),
              'headstones'     =&gt; array(self::HAS_MANY, 'Headstone', 'record_id'),
              'other_names'     =&gt; array(self::HAS_MANY, 'OtherName', 'record_id'),
              'users'          =&gt; array(self::MANY_MANY, 'Users', 'record_owner(record_id, user_id)'),
              'record_owner'   =&gt; array(self::HAS_MANY, 'RecordOwner', 'record_id'),
          );
      }
      ...
  }
  </code></pre>
  
  <p><strong>RecordOwner</strong></p>
  
  <pre><code>class RecordOwner extends CActiveRecord {
      ...
      public function relations() {
          // NOTE: you may need to adjust the relation name and the related
          // class name for the relations automatically generated below.
          return array();
      }
      ...
  }
  </code></pre>
  
  <h1>Problem</h1>
  
  <p>I have updates the search added a with condition on record_owner to the CDbCriteria, I have added a compare on record_owner.user_id but am now getting SQL errors.</p>
  
  <p><strong>search()</strong></p>
  
  <pre><code>public function search() {
      // Warning: Please modify the following code to remove attributes that
      // should not be searched.
  
      $criteria = new CDbCriteria;
  
      $criteria-&gt;compare('id', $this-&gt;id);
      $criteria-&gt;compare('qr_id', $this-&gt;qr_id);
      $criteria-&gt;compare('given_name', $this-&gt;given_name, true);
      $criteria-&gt;compare('family_name', $this-&gt;family_name, true);
      $criteria-&gt;compare('madin_name', $this-&gt;madin_name, true);
      $criteria-&gt;compare('dob', $this-&gt;dob, true);
      $criteria-&gt;compare('dod', $this-&gt;dod, true);
      $criteria-&gt;compare('country_of_birth', $this-&gt;country_of_birth, true);
      $criteria-&gt;compare('country_of_death', $this-&gt;country_of_death, true);
      $criteria-&gt;compare('gender', $this-&gt;gender, true);
      $criteria-&gt;compare('title', $this-&gt;title, true);
  
      $criteria-&gt;with = array('record_owner');
      $criteria-&gt;compare( 'record_owner.user_id', $this-&gt;owner, true );
  
      return new CActiveDataProvider(
          $this,
          array(
               'criteria'   =&gt; $criteria,
               'pagination' =&gt; array(
                   'pageSize' =&gt; Yii::app()-&gt;params['pageSize'],
               )
          )
      );
  }
  </code></pre>
  
  <p><strong>SQL Error</strong></p>
  
  <pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'record_owner.user_id' in 'where clause'. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`qr_id` AS `t0_c1`, `t`.`given_name` AS `t0_c2`, `t`.`family_name` AS `t0_c3`, `t`.`madin_name` AS `t0_c4`, `t`.`dob` AS `t0_c5`, `t`.`dod` AS `t0_c6`, `t`.`country_of_birth` AS `t0_c7`, `t`.`country_of_death` AS `t0_c8`, `t`.`gender` AS `t0_c9`, `t`.`profile` AS `t0_c10`, `t`.`epitaph` AS `t0_c11`, `t`.`site_id` AS `t0_c12`, `t`.`title` AS `t0_c13` FROM `record` `t` WHERE (record_owner.user_id LIKE :ycp0) ORDER BY `t`.`given_name` LIMIT 25 
  </code></pre>
  
  <h1>Question</h1>
  
  <p>How should I be doing this Join and Filter?</p>