Yii Framework Forum: sql count & CActiveDataProvider - Yii Framework Forum

Jump to content

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

sql count & CActiveDataProvider Rate Topic: -----

#1 User is offline   ryland22 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 05-March 11

Posted 19 May 2011 - 07:35 PM

I have a sql query where I want to left join one table on another and count the joined tables rows. The actual query looks like this and it works fine:

SELECT Topic.topicId, description, startDate, count(rantId) FROM Topic left join Rant on Rant.topicId = Topic.topicId


My Topic model has the following to allow a left join with the Rant table:

	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'rant' => array(self::HAS_MANY, 'Rant', 'topicId'),
		);
	}


My controller has the following code to execute the sql query and build a dataProvider:

		$criteria=new CDbCriteria(array(
			'select'=>'description, startDate, count(rantId) as rantCount',
			'with'=>array('rant'),
			'together'=>true,
			'order'=>'createDate desc',
			
		));

		$dataProvider=new CActiveDataProvider('Topic', array(
			'pagination'=>array(
				'pageSize'=>15,
			),
			'criteria'=>$criteria,
		));
		$this->render('view',array(
			'dataProvider'=>$dataProvider,
		));



In the view I have the following:

<?php $this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'=>$dataProvider,
	//'filter'=>$model,
    'columns'=>array(
		array(
			'name' => 'Topic',
			'value' => '$data->description',
		),
		array(
			'name'=> 'Date listed',
			'value'=> '$data->startDate',
		),
		array(
			'name'=> 'Rants',
			'value'=> '$data->rant->rantCount',
		),
	),
));
?>


The problem is that $data->rant->rantCount has no value. The sql runs fine, and I've run the sql output from yii and it also brings back the data fine. How come 'rantCount' has no data in the $dataProvider object? I am at my wits end on how am I supposed to do this?
0

#2 User is offline   jacmoe 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 2,601
  • Joined: 10-October 10
  • Location:Denmark

Posted 19 May 2011 - 07:47 PM

It's easy! :)

        public function relations()
        {
                // NOTE: you may need to adjust the relation name and the related
                // class name for the relations automatically generated below.
                return array(
                        'rant' => array(self::HAS_MANY, 'Rant', 'topicId'),
                        'rantCount' => array(self::STAT, 'Rant', 'topicId'),
                );
        }

That's it.
No need to write separate queries to get the rantCount. Try it. :)
"Less noise - more signal"
1

#3 User is offline   ryland22 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 36
  • Joined: 05-March 11

Posted 19 May 2011 - 09:08 PM

jacmoe - you are a Yii ninja. That worked perfect - thanks!
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