Yii Framework Forum: COUNT(*) - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

COUNT(*) Rate Topic: -----

#1 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 08 November 2009 - 07:37 AM

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

#2 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 08 November 2009 - 09:34 AM

View Postbas_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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#3 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 08 November 2009 - 10:30 AM

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?
0

#4 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 08 November 2009 - 10:52 AM

View Postbas_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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#5 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 08 November 2009 - 11:10 AM

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

0

#6 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 08 November 2009 - 11:13 AM

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)

0

#7 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 09 November 2009 - 10:43 AM

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)

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users