Cactivedataprovider/cdbcriteria And Group By Issue

Yii 1.1 General Discussion
1

Hi there,

I don’t know why but when I add a group condition to my CDbCriteria I get an error.

Here’s the code




        $criteria = new CDbCriteria();        

        $criteria->alias = 'u';

        $criteria->join = 'LEFT JOIN tbl_complaint ON tbl_complaint.id_mission = u.id ';        

        $criteria->condition = " tbl_complaint.id_client = :clientId";

        $criteria->order = ' u.start_date_time DESC';

        $criteria->group = ' tbl_complaint.id_mission';

        $criteria->params = array(':clientId' => $clientId);


        $dp = new CActiveDataProvider('Mission', array(

            'criteria' => $criteria,

            'pagination' => array(

                'pageSize' => 5,

        )))

This generates the error




CDbCommand failed to execute the SQL statement: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'. The SQL statement executed was: SELECT COUNT(*) FROM (SELECT * FROM `tbl_mission` u LEFT JOIN tbl_complaint ON tbl_complaint.id_mission = u.id WHERE tbl_complaint.id_client = :clientId AND tbl_complaint.solved = 0 GROUP BY tbl_complaint.id_mission) sq. Bound with :clientId='407'

If I don’t use “$criteria->group = ’ tbl_complaint.id_mission’;” it does work.

Where is that SELECT COUNT(*) coming from ? Why “Duplicate column name ‘id’” as I explicitly give the name of the table for every field… I am stuck…

Any idea how to solve this issue?

Thanks

Renaud

2

What database are you using?

The SELECT COUNT(*) is added by the CActiveDataProvider. It’s executing one extra query to count the results before fetching them.

If you want to use GROUP BY in a query try setting the ‘select’ attribute of CDbCriteria to specific columns, not ‘*’.

3

i think nineinchnick was right answer


 $criteria->select = 'id_mission',
4

Thanks a lot it worked.

I literally had to put every column name in $criteria->select = …

Thanks

Renaud