$criteria->select only returning first line of sql

Yii 1.1 General Discussion
1

I’m currently trying to shoehorn sql into my criteria as it’s a bit complex for me to put into cdbcriteria at the minute.

The issue is I’m only getting the very first result of the sql and I’m a little bit confused at why it’s not getting the entire query. I feel like I shouldn’t have to loop round as the select query should be grabbing everything and giving it a name for me to use, but apparently not!

My criteria is


public function search() {

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

        // should not be searched.


        $criteria = new CDbCriteria;


        $criteria->select = [

            'userVariables.*',

            't.*',

            'MAX(CASE WHEN userVariables.user_type_variables_id = 1 THEN userVariables.value ELSE NULL END) AS Question1',

            'MAX(CASE WHEN userVariables.user_type_variables_id = 2 THEN userVariables.value ELSE NULL END) AS Question2',

            'MAX(CASE WHEN userVariables.user_type_variables_id = 3 THEN userVariables.value ELSE NULL END) AS Question3',

            'MAX(CASE WHEN userVariables.user_type_variables_id = 4 THEN userVariables.value ELSE NULL END) AS Question4',

            'MAX(CASE WHEN userVariables.user_type_variables_id = 5 THEN userVariables.value ELSE NULL END) AS Question5',

            'MAX(CASE WHEN userVariables.user_type_variables_id = 6 THEN userVariables.value ELSE NULL END) AS Question6',    

        ];


        $criteria->group = "t.id";


        $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->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);


        $criteria->compare('event.name', $this->event_name, true);


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


        $criteria->with = array('user','event','userVariables');

        $criteria->together = true;


        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,

        ));

    }

Using alternative methods that I can’t use here, I was able to return all results using

$count=Yii::app()->db->createCommand($query)->queryScalar();

combined with the csqldataprovider, but I need to get it working within the criteria.

My relations are


 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(

            'event' => array(self::BELONGS_TO, 'Event', 'event_id'),

            'user' => array(self::BELONGS_TO, 'User', 'user_id'),

            'invitedBy' => array(self::BELONGS_TO, 'User', 'guest_of_user_id'),

            'userVariables' => array(self::HAS_MANY, 'UserVariables', 'id', 'alias'=>'userVariables'),

            'userTypeVariables' => array(self::HAS_MANY, 'UserTypeVariables', 'user_id'),

            'groupNames' => array(self::BELONGS_TO, 'EventAttendeesGroupName', 'assign_group'),

        );

    }

The raw sql that returns all the fields I need is


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
2

Hi,

Did u execute raw query inside phpmyadmin or anyother database UI. What result it returned…is it also returning only one result

3

If I run the SQL in phpmyadmin it returns all results. If I run the query as a select in yii criteria, it returns only one line of data (only if I stop it grouping by ID).

it also returns all results if I use a CSqlDataProvider

4

In your code you have:


        $criteria->group = "t.id";


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

I believe that’s where the trouble is. You are settings id = $this->id which will return single id for table and then you are finding all the associated records for only one id. Finally you are doing group by onto that id resulting single record.

5

If I comment out


  $criteria->group = "t.id";

then all the results appear, BUT the results in the criteria->select are now appearing in teh wrong row.