Counting all rows of modelA which exist in modelB

Yii 1.1 General Discussion
1

This is a common sql statement:




SELECT COUNT(*)


FROM tableA


WHERE tableA.id IN


(


 SELECT tableA_id FROM tableB


);

Maybe I am stuck, but how someone performs the same count with Yii?

This is what worked for me:

the code below is the body of a public static function of modelA




$criteria=new CDbCriteria();

		$criteria->with = 'modelBs';

		$criteria->together = true;

		$criteria->condition = 'tableA_id = t.id';

		return self::model()->count($criteria);

Although I reached to the right calculation after a little ‘hack’ since the $criteria->condition is written according to the SQL statement as I saw it in the Exception report.

So how is the right way? (:

2

hi, i think that you need replace "condition" by "addInCondition" to solve your problem… ^^

(Cf: link )

3

Create a relation of type STAT between modelA and modelB, then you can access the count when you need it simply by referencing the property name of the relation.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#statistical-query

4

Shizo 971 thanks for replying but the addInCondition is not the efficient way we are trying to use. You have to use multiple sql statements and have PHP execute foreach loops etc. Not efficient in large queries

Dana first of all thank you a lot since you remind me of the statistical query. Yii framework is huge after all and I am still learning!

However the statistical query is counting the child rows! The manual says it is used mainly for HAS_MANY queries. Maybe there is a way to implement it using the statistical query… I am not sure yet. I would like to count the parent in my situation.

Altering my own CDbCriteria above I am getting the same value:




$criteria=new CDbCriteria();

$criteria->with = 'modelBs';

$criteria->together = true;

$criteria->condition = 'modelBs IS NOT NULL';

return self::model()->count($criteria);

I believe this is more acceptable ;)