CODEWITHSUNDEEP
phpmysqlsqlyii
Ahsan Mohyuddin
Ahsan Mohyuddin

Reputation: 181

Yii relation generates GROUP BY clause in the query

I have User, Play and UserPlay model. Here is the relation defined in User model to calculate total time, the user has played game. 

'playedhours'=>array(self::STAT, 'Play', 'UserPlay(user_id,play_id)', 
'select'=>'SUM(duration)'),

Now i am trying to find duration sum with user id.

$playedHours = User::model()->findByPk($model->user_id)->playedhours)/3600;

This relation is taking much time to execute on large amount of data. Then is looked into the query generated by the relation. 

SELECT SUM(duration) AS `s`, `UserPlay`.`user_id` AS `c0` FROM `Play` `t` INNER JOIN    
`UserPlay` ON (`t`.`id`=`UserPlay`.`play_id`) GROUP BY `UserPlay`.`user_id` HAVING    
(`UserPlay`.`user_id`=9);

GROUP BY on UserPlay.user_id is taking much time. As i don't need Group by clause here.

My question is, how to avoid GROUP BY clause from the above relation.

Upvotes: 2

Views: 1822

Answers (1)

Manquer
Manquer

Reputation: 7647

STAT relations are by definition aggregation queries, See Statistical Query. You cannot remove GROUP BY here and make a meaningful query for aggregate data. SUM(), AVG(), etc are all aggregate functions see GROUP BY Functions, for a list of all aggregate functions supported by MYSQL.

Your problem is for the calculation you are doing a HAVING clause. This is not required as HAVING checks conditions after the aggregation takes place, which you can use to put conditions like for example SUM(duration) > 500 .

Basically what is happening is that you are grouping all the users separately first, then filtering for the user id you want. If you instead use a WHERE clause which will filter before not after then aggregation is for only the user you want then group it your query will be much faster.

Although Active Record is good at modelling data in an OOP fashion, it actually degrades performance due to the fact that it needs to create one or several objects to represent each row of query result. For data intensive applications, using DAO or database APIs at lower level could be a better choice

Therefore it is best if you change the relation to a model function querying the Db directly using the CommandBuilder or DAO API. Something like this 

Class User extends CActiveRecord {

....
    public function getPlayedhours(){
        if(!isset($this->id)) // to prevent query running on a newly created object without a row loaded to it
            return 0;
        $played = Yii::app()->db->createCommand()
               ->select('SUM(duration)')
               ->from('play')
               ->join("user_play up","up.play_id = play.id")
               ->where("up.user_id =".$this->id)
               ->group("up.user_id")
               ->queryScalar();
        if($played == null)
           return 0;
        else 
          return $played/3600 ; 
       }
....
}

If you query still is slow, try optimizing the indexes, implement cache mechanism, and use the explain command to figure out what is actually taking more time and more importantly why. If nothing is good enough, upgrade your hardware.

Upvotes: 2

Related Questions