Count on relations

Yii 1.1 General Discussion
1

Hi All,

I would like to know if there is a way to count the records retrieved when a relation is used.

Example:

Supposing we have a User table with a HAS_MANY relation to a table "address"




$user = User::model()->findByPk(1234);


echo $user->addresses->count(); <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />??

If we can’t do it like this, what is the quickest/best way to do so?

Thanks in advance!

2

Hi,

i think you can use a STAT relation (if you’re using activeRecord):

http://www.yiiframework.com/doc/api/1.1/CStatRelation/

3

Thanks for your reply!

I read the documentation, but I don’t quite understand in what way it is statistical…

Do you have a simple example of how can I use it to do what I want?

4

For instance:




public function relations()

{

	return array(

...

	   'relStudentSemesters' => array(self::HAS_MANY, 'RelStudentSemester', 'group_id'),

	   'studCount'=>array(self::STAT, 'RelStudentSemester', 'group_id'), 

...

	);

}

The table group is related to the table relStudentSemesters, and i use a stat field (which is not in the database, by definition) to have direct response to the question: how much RelStudentSemesters are related to the group number x.

5

As i understand relations are arrays, so why not to use:


count($user->addresses);

?

6
  • count($user->addresses);

$user->addresses <=> sql = "SELECT *FROMaddressASaddressLEFT OUTER JOINuserASuserON (address.user_id=user.id) WHEREuser.id` = 1;

result get many recdords.

count($user->addresses) = count array php.

"if 1 million records, it is dangerous for your system"

  • use stat relations:

$user->stat_relation_count <=> SELECT user_id AS u, COUNT(*) AS s FROM addresses t WHERE (t.user_id=‘2’) GROUP BY user_id

result get 1 record, this count record by mysql.

you can use yii toolbar see sql, and use EXPLAIN query check performance two sql. relation STAT is more optimal.

very happy if my comments help you!

I can not write good English. I hope you to understand my interpretation.

Neo;

7

For anyone, who came to this thread to find out a way on how to get count of related items without writing STAT relation by hands - I wrote an extension for this: http://www.yiiframework.com/extension/yii-auto-stat-relation/

8

it works for me.

$postcount = Post::model()->count();