Sort Dataprovider by sum in related table

Yii 2.0 General Discussions
1

I have 2 tables Event and Ticket, every Event entry can have multiple tickets.

In Ticket table I have a collumn called "quantity"

I want to show up the events order by the quantity of tickets.

How can I achive this?

In Event model I have this function that give me the total os tickets on an event




public function getTotalTickets()

    {

        return $this->hasMany(Ticket::className(), ['event_id' => 'id'])->sum('quantity');

    }

In my controller I have a activeDataProvider but I can’t sort the entries by the number of tickets




$dataProvider = new ActiveDataProvider([

                'query' => Event::find()->where(['status' => Event::STATUS_PUBLISHED, 'country_code' => $countryCode]),

                'sort'=> ['defaultOrder' => ['order' => SORT_DESC, 'views' => SORT_DESC, 'start_date' => SORT_ASC]],

                'pagination' => false

            ]);

The code above order by the event by I want to replace this order for something like "totalTickets" => SORT_DESC.

2

You could create a SQL View in order to sort column "quantity".

3

Yeah, i`m thinking that creating another column for tickets sum in your DB Table would be much cleaner and the easiest way to archive what you need.

I`m wondering, what would happen if u would sort in your search Model with using that relation?