Yii Framework Forum: Difficult query - Yii Framework Forum

Jump to content

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

Difficult query Rate Topic: ***** 1 Votes

#1 User is offline   frantic 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 172
  • Joined: 01-March 10
  • Location:Saint-Petersburg

Posted 11 April 2010 - 07:35 AM

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

0

#2 User is offline   vamp 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 181
  • Joined: 30-January 09

Posted 11 April 2010 - 08:44 AM

Try to use ::STAT relation in Cats model (photosCount etc.)
-Posted Image-
Thanks, you make me stronger!
1

#3 User is offline   frantic 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 172
  • Joined: 01-March 10
  • Location:Saint-Petersburg

Posted 11 April 2010 - 08:57 AM

View Postvamp, on 11 April 2010 - 08:44 AM, said:

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
0

#4 User is offline   frantic 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 172
  • Joined: 01-March 10
  • Location:Saint-Petersburg

Posted 11 April 2010 - 09:23 AM

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

#5 User is offline   oxigen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 30
  • Joined: 05-February 09

Posted 11 April 2010 - 10:41 PM

View Postfrantic, on 11 April 2010 - 09:23 AM, said:

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;

1

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