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

Yii 1.1 General Discussion
1

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

2

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 ?

3

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…