Is There A Way To Remove The Group By Added By Self::stat Relations?

Hi everyone,

We just finished our first yii application and i’ve been assigned the optimization of our queries and databases.

One of the queries i’ve been having issues, is one produced by a self::STAT relation like the one bellow




public function relations()

{

 return array(

      'total_stages' => array(self::STAT, 'Stage', 'work_id'),

    	);

}



This is from the model Work, it is a simple stat relation and on model Stage which has a primary key ‘id’ and a ‘work_id’ foreign key relation, back to Work.

The query that this relation produces seems to be the following.




SELECT `work_id` AS `c`, COUNT(*) AS `s` FROM `stage` `t` WHERE (`t`.`work_id`='2') GROUP BY `work_id`



The issue comes from the GROUP BY statement, if you pass the query through mysql client’s describe command i get something like (notice the Extra column)




mysql> describe SELECT `work_id` AS `c`, COUNT(*) AS `s` FROM `stage` `t` WHERE (`t`.`work_id`='2') GROUP BY `work_id`;

+----+-------------+-------+------+-------------------+---------+---------+-------+------+-----------------------------------------------------------+

| id | select_type | table | type | possible_keys     | key     | key_len | ref   | rows | Extra                                                     |

+----+-------------+-------+------+-------------------+---------+---------+-------+------+-----------------------------------------------------------+

|  1 | SIMPLE      | t     | ref  | work_id,work_id_2 | work_id | 4       | const |    3 | Using where; Using index; Using temporary; Using filesort |

+----+-------------+-------+------+-------------------+---------+---------+-------+------+-----------------------------------------------------------+

1 row in set (0.03 sec)



Notice that by simply removing the ‘GROUP BY’ statement i get far better results, something like.




mysql> describe SELECT `work_id` AS `c`, COUNT(*) AS `s` FROM `stage` `t` WHERE (`t`.`work_id`='2');

+----+-------------+-------+------+-------------------+---------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys     | key     | key_len | ref   | rows | Extra       |

+----+-------------+-------+------+-------------------+---------+---------+-------+------+-------------+

|  1 | SIMPLE      | t     | ref  | work_id,work_id_2 | work_id | 4       | const |    3 | Using index |

+----+-------------+-------+------+-------------------+---------+---------+-------+------+-------------+

1 row in set (0.00 sec)



So my question is,

Is there a way to stop the self::STAT from using ‘GROUP BY’ on some of the relations?

Thanks in advance

pantelis

I have the same situation except mysql EXPLAIN said:

[b]Using where; Using index for group-by[/b]

Why do Yii adds GROUP BY statment to all statistics queries ?

Hi maxl,

I never found an answer to this, my understanding, now, is that self::STAT is used to count only unique occurences of said relations.

As a workaround you could create a method on your model that does something like




Yii::app()->db->createCommand()

     ->select('count(*)')

      ->from('stage')

      ->where("your condition here")

      ->queryScalar();



I hope that helps…