Sort dataprovider with count on related field

I’ve two tables related (Company hasMany Reviews).

I need to list the companies (custom list no grid) sorted by the number of reviews received.

I’m tryng to customize my search function with a “joinWith([‘reviews’])”, but anytime i try to work with the count adding a select i face with some error.

Thanks

No way? Maybe the question is not clear?

The following section of the guide is useful for this scenario.

ActiveRecord - Selecting extra fields

The particular example for your use case might be like this:




class Company extends \yii\db\ActiveRecord

{

    public $reviewsCount;


    // ...


    public function getReviews()

    {

        return $this->hasMany(Review::className(), ['company_id' => 'id']);

    }

}


...


class CompanySearch extends Company

{

    // ...


    public function search($params)

    {

        $query = Company::find()

            ->select([

                '{{company}}.*', // select all company fields

                'COUNT({{review}}.id) AS reviewsCount' // calculate reviews count

            ])

            ->joinWith('reviews') // ensure table junction

            ->groupBy('{{company}}.id'); // group the result to ensure aggregation function works


        // ...

    }

}



Wow, that works! I was sure to have tried that solution. Anyway, now it works!

Just to go a little bit deeper, maybe it could be useful to other people. I would like to sort for "most rated" companies and "top rated" company. This is my solution (CompanySearch->search($params) method):




$query->select([

            '{{companies}}.*', // select all company fields

        ]);


        if(isset($params['order'])) {

            switch($params['order']) {

                case 'most_rated':

                $query->addSelect(['COUNT({{reviews}}.id) AS reviewsCount'])

                ->joinWith('reviews') // ensure table junction

                ->groupBy('{{companies}}.id') // group the result to ensure aggregation function works

                ->orderBy(['reviewsCount' => SORT_DESC]);  

                    break;

                case 'top_rated':

                $query->addSelect(['AVG({{reviews}}.rating) AS avgRating'])

                ->joinWith('reviews') // ensure table junction

                ->groupBy('{{companies}}.id') // group the result to ensure aggregation function works

                ->orderBy(['avgRating' => SORT_DESC]);                

                    break;

            }

        }



So, in the company’s list page i have 2 links to order the list (they could be even 10 in the future). When the search method match the order’s parameter value, i add a select to the main select with the proper query.

What do you think about it?

I would rather go like this:




class Company extends \yii\db\ActiveRecord

{

    public $reviewsCount;

    public $avgRating;

    public $something;


    // ...

}






    public function search($params)

    {

        $query = Company::find()

            ->select([

                '{{company}}.*',

                'COUNT({{review}}.id) AS reviewsCount',

                'AVG({{review}}.rating) AS avgRating',

                '<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' /> AS something',

            ])

            ->joinWith('reviews')

            ->groupBy('{{company}}.id');


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

            'sort' => [

                'attributes' => [

                    'name',

                    'address',

                    'phone',

                    // any other columns of company by that the result should be ordered

                    'reviewsCount',

                    'avgRating',

                    'something',

                ],

                'defaultOrder' => ['name' => SORT_ASC],

            ]

        ]);




Because I would probably want to display both the count of reviews and the average rating in a list of companies, I would select both of them in the query. And I would not hesitate to add more.

In this way, you can treat those extra fields in the same manner as you do with the normal fields. So I would let the sort object of the dataprovider take care of the sorting with them. It also means that you can use them directly as the columns of GridView (now the user can sort the list by clicking on the sort link of the header row).

Also note that you can filter the result using those extra fields:




        if ($this->onlyNotableCompanies) {

            $query->andFilterWhere(['>', 'reviewsCount', Review::NOTABLE_REVIEW_COUNT]);

        }

        if ($this->onlyExcellentCompanies) {

            $query->andFilterWhere(['>', 'avgRaging', Review::EXCELLENT_RATE]);

        }



I think you are right, my only question is: don’t you think that there could be a problem about performance with this kind of process? If I should continue to increase query complexity, maybe with other tables (so increasing joins), I’m pretty sure i will have so many performance problems, especially when the number of records will be higher

I’d rather want to keep the code simple until it actually hit performance.

I agree that too many extra fields with complex selection might cause a performance problem.

But, after all, that’s a problem arising from the layer of RDB, not from Yii’s ActiveRecord. We should experience the same problem even if we use raw SQL. Then, it’s time for us to reconsider the overall design of the database and the application.