COUNT(*)

how can i realize this in yii:




SELECT t.Tag, COUNT( dt.TagId ) AS Number

FROM Tags t

INNER JOIN DaytripTag dt ON t.IdTag = dt.TagId

GROUP BY t.Tag



at the moment i have a in my model Tag:




    public function relations() {

        return array(

            'rDaytripTag' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)',

                'select' => array('COUNT(*) AS Number'),

                'group' => 'Tag'

            )

        );

    }



but printing $tag->rDaytripTag->Number; wil print nothing. when i print $tag->Tag it will print the tag.

Try




$tag->rDaytripTag[$i]->Number;



or foreach(…)

/Tommy

i changed the relation to




    public function relations() {

        return array(

                'tagCount' => array(self::STAT, 'Tag', 'DaytripTag(DaytripId, TagId)'

            )

        );

    }



and print it with <?php echo $daytrip->rTag2[0]->gTags; ?>

this the right way to do this?

Referring to your SQL example, the self::STAT would return “COUNT( t.TagId )”, wouldn’t it. Check the generated SQL (CFileLogRoute, CWebLogRoute).

/Tommy

A side note: Sometimes if you have a complex SQL query it might not be worth the effort to realize everything with pure AR methods. You can always add a custom method to your active record that performs your SQL. There’s nothing wrong with this approach and it will even be faster since AR doesn’t have to create the SQL for you.


public function countDaytrips() {

  $cmd = $this->getDbConnection()->createCommand('<YOUR SQL HERE...>');

  return $cmd->queryRow(true);

}



Make sure you call this on the model. Otherwhise dbConnection might not be initialized:


Tag::model()->countDaytrips();

your’re right, how can i change that?

why is het doing the count query for every record from the daytips table?




03:31:22.676012

trace

system.db.CDbCommand

Querying SQL: SELECT COUNT(*) AS `s`, `DaytripTag`.`DaytripId` AS `c0` FROM `Tags` INNER JOIN `DaytripTag` ON (`Tags`.`IdTag`=`DaytripTag`.`TagId`) GROUP BY `DaytripTag`.`DaytripId` HAVING (`DaytripTag`.`DaytripId`=691)



i need:




SELECT t.Tag, COUNT( dt.DaytripId )

FROM Tags t

INNER JOIN DaytripTag dt ON t.IdTag = dt.TagId

GROUP BY t.Tag

LIMIT 0 , 30



so why is yii selecting DaytripTag.DaytripId AS c0 and adding DaytripTag.DaytripId to the group by?

Model:




    public function relations() {

        return array(

                'tagCount' => array(self::STAT, 'Tag', 'DaytripTag(DaytripId, TagId)',

                'select' => 'Tag, COUNT( DaytripTag.DaytripId )',

                'group' => 'Tag'



Querying SQL:




SELECT Tag, COUNT( DaytripTag.DaytripId ) AS `s`, `DaytripTag`.`DaytripId` AS `c0`

FROM `Tags`

INNER JOIN `DaytripTag` ON (`Tags`.`IdTag`=`DaytripTag`.`TagId`)

GROUP BY `DaytripTag`.`DaytripId`, Tag

HAVING (`DaytripTag`.`DaytripId`=511)