How do I specify the tablename in a filter in Yii?
There's two tables, attendee
, and group
. both have a gender
field. Each attendee is assigned to 1 group. I've got my CGridView
successfully showing the number of attendees, but when I added code to filter on gender, I get the following error. To calculate the number of attendees in a group, I'm manually performing a join; but the join makes the field 'gender' ambiguous. Is there any way to specify in the filter that it should use the 't' or base table?
When I load the view it operates correctly; when I click to sort on gender
, it gives me this message:
Error Message
> Error 500: <h1>CDbException</h1>
> <p>CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'gender'
> in where clause is ambiguous. The SQL statement executed was: SELECT
> COUNT(*) FROM (SELECT t.*, IFNULL( count(tbl_attendee.id), 0) as
> attendeeCount FROM `tbl_group` `t` LEFT JOIN tbl_attendee ON
> tbl_attendee.group_id = t.id WHERE gender LIKE :ycp0 GROUP BY t.id) sq
> (/Applications/MAMP/htdocs/yiiroot/framework/db/CDbCommand.php:516)</p><pre>#0
> /Applications/MAMP/htdocs/yiiroot/framework/db/CDbCommand.php(411):
> CDbCommand->queryInternal('fetchColumn', 0, Array)
Model Code
$criteria=new CDbCriteria;
$criteria->compare('name',$this->name,true);
$criteria->compare('gender',$this->gender,true);
$criteria->compare('agegroup',$this->agegroup,true);
$criteria->select = 't.*, IFNULL( count(tbl_attendee.id), 0) as attendeeCount';
$criteria->join = 'LEFT JOIN tbl_attendee ON tbl_attendee.group_id = t.id';
$criteria->group = 't.id';
$sort = new CSort();
$sort->attributes = array(
'attendeeCount'=>array(
'asc'=>'attendeeCount ASC',
'desc'=>'attendeeCount DESC',
),
'*', // add all of the other columns as sortable
);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
'sort'=>$sort,
));
View Code
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'group-grid',
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
'name',
开发者_运维百科 array(
'name'=>'gender',
'value'=>'Group::$genderLabels[$data->gender]',
'filter'=>Group::$genderLabels, // <-- This is the filter that breaks.
// $genderLabels = array('Female','Male);
),
Ah! I got it.
Changed this line in the model code:
$criteria->compare('gender',$this->gender,true);
to
$criteria->compare('t.gender',$this->gender,true);
Found the answer here.
精彩评论