How to use sub-query aggregate calculation in condition for a DataProvider?

Yii 1.1 General Discussion
1

Hi, I’m using this subquery in the select to overcome the impossibility of using a STAT relation in the criteria of a DataProvider.

That works fine. But when I try to add this condition to the criteria: subscriber_count > 50 it breaks and gives errors about the subscriber_count field in the where clause not found, because Yii is apparently internally building a COUNT() query and the subquery is not included there.

I tried including the whole subquery in the condition, but then everything breaks, the model’s relations don’t work anymore, so that doesn’t work either.

What should I do to make it work?


$authors = new CActiveDataProvider( 'User', [

    'criteria' => [

        // 'condition' => 'subscriber_count > 50',

        'select' => [

            't.*', 

            '(select count(*) from subscriber_author subaut where subaut.author_id = t.id AND subaut.verified >= 1) as subscriber_count'

    ]],

    'sort' => [

        'defaultOrder' => [ 'subscriber_count' => CSort::SORT_ASC ],

        'attributes' => [

            'subscriber_count' => [

                'desc' => 'subscriber_count ASC',

                'asc' => 'subscriber_count DESC'

            ]

]]]);

Note: I would love to update to Yii 2.0 but it’s not an option right now in this project, so even if there’s a better solution in 2.0 I’m stuck with 1.1 for now.

2

Try something like this


'condition' => '(select count(*) from subscriber_author where author_id = t.id and verified >= 1) > 50',

Edit: I have to refresh my SQL knowledge but it seems like the count is available for direct comparison (no need for alias).

3

Yes, I already tried that: