title: How to use this SQL in CDbCriteria
body: <p>I've currently got this SQL </p>
  
  <pre><code> SELECT
  
  
         MAX(CASE WHEN uv.user_type_variables_id = 1 THEN uv.value ELSE NULL END) Question1,
         MAX(CASE WHEN uv.user_type_variables_id = 2 THEN uv.value ELSE NULL END) Question2,
         MAX(CASE WHEN uv.user_type_variables_id = 3 THEN uv.value ELSE NULL END) Question3,
         MAX(CASE WHEN uv.user_type_variables_id = 4 THEN uv.value ELSE NULL END) Question4,
         MAX(CASE WHEN uv.user_type_variables_id = 5 THEN uv.value ELSE NULL END) Question5,
         MAX(CASE WHEN uv.user_type_variables_id = 6 THEN uv.value ELSE NULL END) Question6
  
         FROM tbl_event_attendees AS ea
  
         LEFT JOIN tbl_user AS u ON ea.user_id = u.id
         LEFT JOIN tbl_event AS e ON ea.event_id = e.id                  
         LEFT JOIN tbl_user_variables AS uv on u.id = uv.user_id
  
         GROUP BY ea.id
  </code></pre>
  
  <p>And I need to put it into my existing CDbCriteria + extend the relations. I need each Question 1 to 6 to be a variable I can use to put inside a cgridview but I'm struggling to get it working. If I use CSQLdataprovider it works fine BUT I need the existing criteria I already have.</p>
  
  <p>My current criteria</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, true);
          $criteria-&gt;compare('event_id', $this-&gt;event_id, true);
          $criteria-&gt;compare('status_id', $this-&gt;status_id, true);
          $criteria-&gt;compare('checkin_status_id', $this-&gt;checkin_status_id, true);
  //        $criteria-&gt;compare('guest_invites', $this-&gt;guest_invites, true);
          $criteria-&gt;compare('guest_of_user_id', $this-&gt;guest_of_user_id, true);
          $criteria-&gt;compare('user_id', $this-&gt;user_id, true);
          $criteria-&gt;compare('assign_group', $this-&gt;assign_group, true);
  
  //        $criteria-&gt;with = 'eventAttendeesGroup';
          $criteria-&gt;with = 'user';
          $criteria-&gt;compare('user.forename', $this-&gt;user_forename, true);
          $criteria-&gt;compare('user.surname', $this-&gt;user_surname, true);
          $criteria-&gt;compare('user.company', $this-&gt;user_company, true);
          $criteria-&gt;compare('user.telephone', $this-&gt;user_telephone, true);
          $criteria-&gt;compare('user.dob', $this-&gt;user_dateofbirth, true);
  
          //need to get rid of this
          $criteria-&gt;compare('userVariables.value', $this-&gt;userType_value, true);
  
          $criteria-&gt;order = 'user.surname ASC';
  
          return new CActiveDataProvider($this, array(
              'criteria' =&gt; $criteria,
              //manages the pagination and how many users appear on the onsite reg page 
              'pagination'=&gt;array('pageSize'=&gt;50),
              //'pagination'=&gt;false,
          ));
      }
  </code></pre>
  
  <p>Anyone got any ideas? </p>
  
  <p>I presume I'd have to set variables for each question and add them to a relation like usertype.variable and $this->variable but at a total loss.</p>
  
  <p>edit.</p>
  
  <p>I can get </p>
  
  <pre><code>     function newsearch(){
       $rawData=Yii::app()-&gt;db-&gt;createCommand('SELECT
  
  
                      MAX(CASE WHEN uv.user_type_variables_id = 1 THEN uv.value ELSE NULL END) Question1,
                      MAX(CASE WHEN uv.user_type_variables_id = 2 THEN uv.value ELSE NULL END) Question2,
                      MAX(CASE WHEN uv.user_type_variables_id = 3 THEN uv.value ELSE NULL END) Question3,
                      MAX(CASE WHEN uv.user_type_variables_id = 4 THEN uv.value ELSE NULL END) Question4,
                      MAX(CASE WHEN uv.user_type_variables_id = 5 THEN uv.value ELSE NULL END) Question5,
                      MAX(CASE WHEN uv.user_type_variables_id = 6 THEN uv.value ELSE NULL END) Question6
  
                      FROM tbl_event_attendees AS ea
  
                      LEFT JOIN tbl_user AS u ON ea.user_id = u.id
                      LEFT JOIN tbl_event AS e ON ea.event_id = e.id                  
                      LEFT JOIN tbl_user_variables AS uv on u.id = uv.user_id
  
                      GROUP BY ea.id')-&gt;queryAll();
  // or using: $rawData=User::model()-&gt;findAll();
  return  $dataProvider=new CArrayDataProvider($rawData, array(
      'id'=&gt;'Questions',
      'sort'=&gt;array(
          'attributes'=&gt;array(
               'Question1', 'Question2', 'Question3',
          ),
      ),
      'pagination'=&gt;array(
          'pageSize'=&gt;10,
      ),
  ));
  </code></pre>
  
  <p>To display the sql in the grid, but I still can't access my model data alongside this.</p>