Yii2: How to order a relation by virtual attribute?

Question

I have a class Group, which hasMany Teams and I would like to order them by their score. A team's score is calculated by iterating through its games, so score is not a db-field, it's a virtual read-only attribute.

This is my attempt:

public function getTeams()
{
    return $this->hasMany(Team::className(), ['group' => 'id'])->orderBy(['score' => SORT_ASC]);
}

This does not work, as score is not a db-field. How can I order a relation by a virtual attribute?

What stops you to add this field in db as you need to use it in queries? I think it's not possible, so you can get unsorted data and sort them later using php sorting functions. — StalkAlex, May 18 '16 at 15:58
Consider the performance, you should save the score into the database after you calculated it. You may calculate the score at 'afterSave()' method of Teams, or other necessary place. — Xiaosong Guo, May 19 '16 at 02:55
@scaisEdge: I don't think these are necessary. searchModel code is the default code generated by gii. — Mathias, May 19 '16 at 15:20

Antonín Slejška · Accepted Answer · 2016-05-19T07:56:38.173

You can create a database view, e.g. team_score. The view should contain all the fields from the team table and a calculated field score (calculated with SQL commands). Then you can create a model TeamScore, which you can quite easily order by the field score.

Disadvantage: The values will be by every calling the view new calculated, what requires more compute power, than when the calculated values are directly stored in the table.

Advantage: It is simple to implement and firstly for displaying tables with many relations it saves a lot of coding.

Yii2: How to order a relation by virtual attribute?

1 Answers1