Scopes, Group, Count - how?

I have this code, which counts how many of each modification are "on the way":





        $sql = ('select count(*) as amount, modification_id

        from products_store

        where  


        products_store.archived=0

        and (received=0 OR (received=1 and place_id=0 and receive_date<(NOW() - INTERVAL 1 DAY)))

        and defected!=1 and sent_to_costumer!=1


        group by modification_id');

        $command = Yii::app()->db->createCommand($sql);

        $dataReader = $command->query();


        foreach($dataReader as $row){

            @$this->_on_the_way[$row['modification_id']]=$row['amount'];

        }



So, "on the way" is a scope, and I want to use it in other places without repeating

[sql]

    archived=0


    and (received=0 OR (received=1 and place_id=0 and receive_date&lt;(NOW() - INTERVAL 1 DAY)))


    and defected&#33;=1 and sent_to_costumer&#33;=1

[/sql]

I defined a scope in ProductOnStore model:





    public function scopes(){

        return array(

            'onTheWay'=>array(

                'condition'=>'

                  products_store.archived=0

                  and (received=0 OR (received=1 and place_id=0 and receive_date<(NOW() - INTERVAL 1 DAY)))

                  and defected!=1 and sent_to_costumer!=1

                 '

            ),

        );

    }




Now I wonder, how to remake first code, using scope.

I don’t see any ways of group and count under scope.

This returns all modifications, even those which have no any productsOnStore





        $items=ProductModification::model()->findAll(

            array(

                'with'=>array(

                    'productsOnStore'=>array(

                        'scopes'=>array(

                            'onTheWay',

                        )

                    ),

                ),

            )

        );



[twitter]MaxZhuravlev[/twitter]

I think you can use the select and group of CDbCriteria =>




 public function scopes(){

        return array(

            'onTheWay'=>array(

                'select' => 'count(*)',

                'condition'=>'

                  products_store.archived=0

                  and (received=0 OR (received=1 and place_id=0 and receive_date<(NOW() - INTERVAL 1 DAY)))

                  and defected!=1 and sent_to_costumer!=1',

                'group' => 'modification_id' 

            ),

        );

    }



I don’t test this…