Difficult query

Hi!

How can I build the following query using Active Records?


SELECT 

	*, 

	( 

		SELECT COUNT(*) 

		FROM photos 

		WHERE cat = a.id 

	) AS qnt

FROM cats AS a

ORDER BY id

This one of course doesn’t work:




$criteria=new CDbCriteria;

$criteria->select='*, ( SELECT COUNT(*) FROM photos WHERE cat = a.id ) AS qnt';

$criteria->order='id ASC';

$criteria->alias='a';


$model=Cats::model()->findAll($criteria);

Try to use ::STAT relation in Cats model (photosCount etc.)

Please can you provide the simpliest example? I can’t find enough information in manual

I understood the working with ::STAT. Thanks a lot!

Add stat relationship in Category model.

Note: Photo class is model of ‘photos’ table.




public function relations()

{

        return array(

            'qnt'=>array(self::STAT, 'Photo', 'cat'),

        );

    }

}






$criteria=new CDbCriteria;

$criteria->order='id ASC';

$cats=Category::model()->with('qnt')->findAll($criteria);

echo $cats->qnt;