search records depending on count of HAS_MANY relation

Yii 1.1 General Discussion
1

I want to show items that are in folders with more than 1 item.

Relational rules of model items:


public function relations() {

    return array(

        'folder'=>array(self::BELONGS_TO, 'folders', 'folder_Id'),

    );

}

I tried this in the search function of the items model:


public function search() {

    $criteria=new CDbCriteria;

    $criteria->group = 'folder_Id';

    $criteria->having = 'COUNT(folder_Id)>1';

    ...

Of course I get only a list with one record of each folder.

This condition retrieves records in folders, that have only 1 item:


    $criteria->group = 'folder_Id';

    $criteria->having = 'COUNT(folder_Id)=1';

That is ok, but how can I resolve the above query?

Sorry, I tried many things, I am confused :blink:

2

I would recommend you write the query in sql first and then translate it to yii criteria


        // assuming you have 2 tables (items, folders) and you have one to many relation

        // folder can have many items and an item belongs to a folder

        $criteria = new CDbCriteria;

        $criteria->with = ['folder'];

        $criteria->select = "folder.name, COUNT(items.id) as total";

        $criteria->group = "folder.name";

        $criteria->having = "total > 1";
3

Thank you for your answer.

I tried your succestion, but I get the same result as before.

Please have a look at my code below (now I use variables in German, because there might be some failure with naming …

The condition is passed from the controller, but this does not matter …


public function relations() {

   return array(

      'Heft'=>array(self::BELONGS_TO, 'Hefte', 'Heft_Id'),

   );

}



public function search($condition = NULL) {

   $criteria=new CDbCriteria;

   $criteria->with = array('Heft');


   echo $condition; // for test purpose I see, that the condition is ok.


   if ( $condition ) {

       $criteria->select = "*, COUNT(Heft.Id) as countItems";

       $criteria->group = "Heft.Id";

       $criteria->having = $condition;

I get the proper result, when the condition is: countItems = 1

When the condition is countItems > 1 then I only get one record of each "Heft".