[bas_vdl]

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.

[tri]

bas_vdl, on 08 November 2009 - 07:37 AM, said:

...
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

[bas_vdl]

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?

[tri]

bas_vdl, on 08 November 2009 - 10:30 AM, said:

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

[Mike]

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();

[bas_vdl]

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)

[bas_vdl]

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)