How to use this SQL in CDbCriteria

've currently got this SQL


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

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.

My current criteria


public function search() {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.


        $criteria = new CDbCriteria;


        $criteria->compare('id', $this->id, true);

        $criteria->compare('event_id', $this->event_id, true);

        $criteria->compare('status_id', $this->status_id, true);

        $criteria->compare('checkin_status_id', $this->checkin_status_id, true);

//        $criteria->compare('guest_invites', $this->guest_invites, true);

        $criteria->compare('guest_of_user_id', $this->guest_of_user_id, true);

        $criteria->compare('user_id', $this->user_id, true);

        $criteria->compare('assign_group', $this->assign_group, true);


//        $criteria->with = 'eventAttendeesGroup';

        $criteria->with = 'user';

        $criteria->compare('user.forename', $this->user_forename, true);

        $criteria->compare('user.surname', $this->user_surname, true);

        $criteria->compare('user.company', $this->user_company, true);

        $criteria->compare('user.telephone', $this->user_telephone, true);

        $criteria->compare('user.dob', $this->user_dateofbirth, true);


        //need to get rid of this

        $criteria->compare('userVariables.value', $this->userType_value, true);


        $criteria->order = 'user.surname ASC';


        return new CActiveDataProvider($this, array(

            'criteria' => $criteria,

            //manages the pagination and how many users appear on the onsite reg page 

            'pagination'=>array('pageSize'=>50),

            //'pagination'=>false,

        ));

    }

Anyone got any ideas?

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.