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