Hi,
i have trouble in filtering and sorting relational field in CgridView.
Here’s the relation:
'relStudentClasses' => array(self::HAS_MANY, 'RelStudentClass', 'student_id', 'together'=>true ),
Search function:
$criteria=new CDbCriteria;
$criteria->compare('t.student_id',$this->student_id);
$criteria->compare('student_lastname',strtoupper($this->student_lastname),true); // on teste la valeur en majuscule
$criteria->compare('student_firstname',strtoupper($this->student_firstname),true); // on teste la valeur en majuscule
$criteria->compare('student_birthdate',$this->student_birthdate,true);
$criteria->compare('student_birthplace_id',$this->student_birthplace_id);
$criteria->compare('civility_id',$this->civility_id);
$criteria->compare('sex_id',$this->sex_id);
$criteria->compare('nationality_id',$this->nationality_id);
$criteria->compare('situation_id',$this->situation_id);
$criteria->compare('disabled',$this->disabled);
$criteria->compare('student_address',$this->student_address,true);
$criteria->compare('student_zip_code',$this->student_zip_code,true);
$criteria->compare('student_mail',$this->student_mail,true);
$criteria->compare('univ_mail',$this->univ_mail,true);
$criteria->compare('stage_id',$this->stage_id);
$criteria->compare('language_id',$this->language_id);
$criteria->with = array( 'civility', 'sex', 'nationality', 'situation', 'stage', 'language', 'department', 'relStudentClasses', );
$criteria->compare( 'civility.civility_name', $this->civility_search, true );
$criteria->compare( 'sex.sex_name', $this->sex_search, true );
$criteria->compare( 'nationality.nationality_name', $this->nationality_search, true );
$criteria->compare( 'situation.situation_name', $this->situation_search, true );
$criteria->compare( 'stage.stage_name', $this->stage_search, true );
$criteria->compare( 'language.language_name', $this->language_search, true );
$criteria->compare( 'department.department_name', $this->department_search, true );
$criteria->compare( 'relStudentClasses.class_id', $this->class_search );
$criteria->compare( 'relStudentClasses.class_status', $this->presence_search, true);
return new CActiveDataProvider(get_class($this), array(
// ajout bloc pour pagesize
'pagination'=>array(
'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),),
// fin ajout bloc pour pagesize
'criteria'=>$criteria,
'sort'=>array(
'attributes'=>array(
'department_search'=>array(
'asc'=>'department.department_name',
'desc'=>'department.department_name DESC',
),
'civility_search'=>array(
'asc'=>'civility.civility_name',
'desc'=>'civility.civility_name DESC',
),
'stage_search'=>array(
'asc'=>'stage.stage_name',
'desc'=>'stage.stage_name DESC',
),
'language_search'=>array(
'asc'=>'language.language_name',
'desc'=>'language.language_name DESC',
),
/* 'class_search'=>array(
'asc'=>'relStudentClasses.class_id',
'desc'=>'relStudentClasses.class_id DESC',
), */
'presence_search'=>array(
'asc'=>'relStudentClasses.class_status',
'desc'=>'relStudentClasses.class_status DESC',
),
'*',
),
),
)
);
// fin modification champs triables
}
And the admin form:
$this->widget('zii.widgets.grid.CGridView', array(
'id'=>'student-grid',
'dataProvider'=>$model->search(),
'selectableRows'=>2, // pour traitement par lot (attention 2 signifie "plus de 1", ce n'est pas la valeur du nb d'occ selectionnables)
'filter'=>$model,
'columns'=>array(
// La premiere colonne est utilisee pour afficher la case à cocher
array(
'header'=>'Sélection',
'value'=>'$data->student_id',
'class'=>'CCheckBoxColumn',
),
// 'student_id',
array(
'header'=>'N° étudiant',
'name'=>'student_id',
'value'=>'$data->student_id',
'htmlOptions'=>array('width'=>'60'),
),
'student_lastname',
'student_firstname',
array('header'=>'filière',
'name'=>'stage_search',
'filter' => CHtml::listData(Stages::model()->findAll(), 'stage_name', 'stage_name'),
'value'=>'$data->stage->stage_name',
),
array('header'=>'Langue',
'name'=>'language_search',
'filter' => CHtml::listData(Languages::model()->findAll(), 'language_name', 'language_name'),
'value'=>'$data->language->language_name' ),
array(
'header'=>'Présence cours',
'name'=>'presence_search',
'type'=>'raw',
'filter'=>array(0=>'---',1=>'présent',2=>'absent'),
'value'=>function($data)
{
$relStudentClassId = array();
$presence = '';
foreach ($data->relStudentClasses as $relStudentClass) {
if($relStudentClass->class_status == 0) {
$presence = '---';
}
if($relStudentClass->class_status == 1) {
$presence = 'Présent';
}
if($relStudentClass->class_status == 2) {
$presence = 'Absent';
}
$relStudentClassId[] = $presence;
}
return implode(', ', $relStudentClassId);
},
'htmlOptions'=>array('width'=>'120'),
),
// fin cours
array(
'class'=>'CButtonColumn',
'htmlOptions' => array('style'=>'width:60px'), // width repassee de 80 a 60
'buttons' => array(
'update' => array(
'url'=>'Yii::app()->createUrl("student/UpdateMultimodel", array("id"=>$data->student_id))',
),
),
// ajout pour pagesize
'header'=>CHtml::dropDownList('pageSize',$pageSize,array(20=>20,50=>50,100=>100),array(
'onchange'=>"$.fn.yiiGridView.update('student-grid',{ data:{pageSize: $(this).val() }})",)),
// fin bloc pour pagesize
),
),
));
The problem is on the presence_search field, when i’m trying to filter, i get the SQL error:
CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "relstudentclasses"
LINE 1: ...ntClasses"."student_id"="t"."student_id") WHERE (relStudent...
^. The SQL statement executed was: SELECT COUNT(DISTINCT "t"."student_id") FROM "Students" "t" LEFT OUTER JOIN "Civilities" "civility" ON ("t"."civility_id"="civility"."civility_id") LEFT OUTER JOIN "Sexes" "sex" ON ("t"."sex_id"="sex"."sex_id") LEFT OUTER JOIN "Nationalities" "nationality" ON ("t"."nationality_id"="nationality"."nationality_id") LEFT OUTER JOIN "Situations" "situation" ON ("t"."situation_id"="situation"."situation_id") LEFT OUTER JOIN "Stages" "stage" ON ("t"."stage_id"="stage"."stage_id") LEFT OUTER JOIN "Languages" "language" ON ("t"."language_id"="language"."language_id") LEFT OUTER JOIN "Departments" "department" ON ("t"."student_birthplace_id"="department"."department_id") LEFT OUTER JOIN "Rel_student_class" "relStudentClasses" ON ("relStudentClasses"."student_id"="t"."student_id") WHERE (relStudentClasses.class_status LIKE :ycp0)
In fact, in the condition, quotes are missing around relStudentClasses.
Does anybody meet similar problem?