Dear all,
I’m a new Yii framework person and I’ve been pretty much happy with all the information available such that I am able to progress in my project up to this point. However, I’ve bumped into a weird glitch, or perhaps I don’t have enough information to do this feature.
The story goes like this.
The related models are ‘Experiment’, ‘Session’, ‘User’, ‘Session-User-Assignment’.
Sessions belong to an Experiment, and has a MANY to MANY relationship with users (i.e. Users can attend many sessions, and many sessions can have many users).
In a particular view, I would like to show a list of sessions that are of a certain date, and enable sorting according to several attributes. The usual attributes that are entered in the database of the Session model and it’s related models (such as Experiment) has no issues of sorting or displaying in either CListView or CGridView.
However I’d like to enable sorting for a calculated value of (1) the number of users in a session and (2) the number of empty slots in the session.
After looking at a few forum posts, I have learnt that I cannot just sort using calculated values provided in the Session model via functions. I have to use an SQL statement to calculate and provide the values as aliases for Yii to enable sorting as Yii does it via an SQL ORDER BY method. (So far am I right? Correct me if I’m wrong).
Therefore, I have chosen to use this chunk of code for the CActiveDataProvider to pass to the view.
public function actionCalendar()
{
$y = $_GET['year'];
$m = $_GET['month'];
$d = $_GET['day'];
$date = Yii::app()->dateFormatter->format('yyyy-MM-dd', CDateTimeParser::parse($y.'-'.$m.'-'.$d, 'yyyy-M-d'));
$sort=new CSort;
$sort->attributes=array(
'start_time',
'name',
'attendee_no',
'available'=>array(
'asc'=>'available',
'desc'=>'available DESC',
'label'=>'Experiment Status'),
'emptySlots'=>array(
'asc'=>'emptySlots',
'desc'=>'emptySlots DESC',
'label'=>'Available Slots'),
);
$sort->defaultOrder='start_time';
$criteria=new CDbCriteria;
$criteria->select = array(
'*',
'experiment.status_id AS available',
'COUNT(*) AS emptySlots', //THIS IS THE PROBLEM LINE
);
$criteria->with = array(
'experiment',
);
$criteria->together=true;
$criteria->condition = 'date=:date AND experiment.status_id>0';
$criteria->params = array(':date'=>$date);
$dataProvider = new CActiveDataProvider('Session', array(
'criteria'=>$criteria,
'pagination'=>array(
'pageSize'=>10,
),
'sort'=>$sort,
));
$this->render('calendar',array(
'dataProvider'=>$dataProvider,
));
}
Let me explain a little and point out the issue.
The action is provided by a controller which displays a list of sessions based on the date (therefore the name actionCalendar and also the part on retrieving dates from the GET global variable)
I then define my own CSort which enables me to sort via Session attributes (name, start_time, attendee_no) and Experiment data (available) which I retrieve via the next section, $criteria, and have defined as public properties of Session and Experiment. So far, there’s no problem.
The issue comes in when I try to add in a COUNT function via SQL in the next line.
'COUNT(*) AS emptySlots'
When I put this line in, the view only displays a single Session.
If I remove the line, it will go back to displaying all available sessions (say 3, if I have 3 sessions that have experiment.status_id>0 and on the particular date).
What is the issue here?