title: How to add where in Yii model search method?
body: <p>I have four tables in my DB as shown(user, user_test, test, status). There is a M:M to relation between user and test and therefore user_test is a Gerund between them.
  The status table has statuses for both user_test and test tables. The tablename field in status table shows which table the status belongs to as shown in the lower image.</p>
  
  <p>I want to show table user_test in a CGridView with the related data in all the three tables user, test and status. All is well as the relations are correct.</p>
  
  <p>Problem: When I want to show status.name in my CGrid like:</p>
  
  <pre><code>array(
      'header'=&gt;'Status',
      'value'=&gt;'$data-&gt;status-&gt;name',
  ), 
  </code></pre>
  
  <p>It gives me name 'completed' but it is the status of table 'test' and the correct one should be 'confirmed' as status_id in user_test is 2.</p>
  
  <p>Any help?</p>
  
  <p><a href="http://www.softhem.se/blog/wp-content/uploads/2013/07/db.png" rel="nofollow">Database design</a>
  <a href="http://www.softhem.se/blog/wp-content/uploads/2013/07/status.png" rel="nofollow">Status table</a></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;with=array('user','test','status');
  
      $criteria-&gt;compare('id',$this-&gt;id);
      $criteria-&gt;compare('user_id',$this-&gt;user_id);
      $criteria-&gt;compare('test_id',$this-&gt;test_id);
      $criteria-&gt;compare('status.id',$this-&gt;status_id);
      $criteria-&gt;compare('bonus',$this-&gt;bonus);
      $criteria-&gt;compare('user.signum',$this-&gt;signum, FALSE);
      $criteria-&gt;compare('user.email',$this-&gt;email, FALSE);
      $criteria-&gt;compare('test.seats',$this-&gt;seats, FALSE);
  
      return new CActiveDataProvider($this, array(
          'criteria'=&gt;$criteria,
          'sort'=&gt;array(
              'attributes'=&gt;array(
                  'signum'=&gt;array(
                      'asc'=&gt;'user.signum',
                      'desc'=&gt;'user.signum DESC',
                  ),
                  'email'=&gt;array(
                      'asc'=&gt;'user.email',
                      'desc'=&gt;'user.email DESC',
                  ),
                  'seats'=&gt;array(
                      'asc'=&gt;'test.seats',
                      'desc'=&gt;'test.seats DESC'
                  ),
                  '*',
              ),
          ),
      ));
  }
  </code></pre>
  
  <p>UPDATE:
  Relations are here:</p>
  
  <pre><code>//UserTest
  ....
  return array(
          'status' =&gt; array(self::BELONGS_TO, 'Status', 'status_id'),
          'test' =&gt; array(self::BELONGS_TO, 'Test', 'test_id'),
          'user' =&gt; array(self::BELONGS_TO, 'User', 'user_id'),
          'testtimeslots'=&gt;array(self::BELONGS_TO, 'TestTimeslots','timeslots_id'),
      );
  
  
   // Test
    return array(
          'testType' =&gt; array(self::BELONGS_TO, 'TestType', 'test_type_id'),
          'status' =&gt; array(self::BELONGS_TO, 'Status', 'status_id'),
          'testCriterias' =&gt; array(self::HAS_MANY, 'TestCriteria', 'test_id'),
          'testTimeslots' =&gt; array(self::HAS_MANY, 'TestTimeslots', 'test_id'),
          'userTests' =&gt; array(self::HAS_MANY, 'UserTest', 'test_id'),
      );
  
  
      // User
      return array(
          'userLanguages' =&gt; array(self::HAS_MANY, 'UserLanguage', 'user_id'),
          'userTests' =&gt; array(self::HAS_MANY, 'UserTest', 'user_id'),
      );
  
      // Status
      return array(
          'tests' =&gt; array(self::HAS_MANY, 'Test', 'status_id'),
          'userTests' =&gt; array(self::HAS_MANY, 'UserTest', 'status_id'),
      );
  </code></pre>
  
  <p>UPDATED: The sql I want to generate is:</p>
  
  <pre><code>        SELECT * FROM user_test AS UserTest
             INNER JOIN user ON user.id=UserTest.user_id
             INNER JOIN test on test.id=UserTest.test_id
             INNER JOIN (SELECT status.value, name from status where  status.tablename='user_test') AS Status ON (Status.value = UserTest.status_id)
  </code></pre>