Lots of OR and AND in an ActiveRecord query?

Question

Yii

The matches table has fields user1, user2, score1, score2 and tour_id
where games are recorded.
the user can be both user1 and user2 depending on where the game is, home or away,
I need to get the total score of user 1 and the total score of user 2 of the desired tournament in order to compare and determine who won.
came up with this query

$score1 = Matches::find()->asArray()->where(['tour_id' => $matche->tour_id, 'part' => $part])->andWhere(['user1' => $matche->user1, 'user2' => $matche->user2])->orWhere(['user1' => $matche->user2, 'user2' => $matche->user1])->sum('score1');

it seems like I get the score of player 1, but I need to get the second score as well and I'm not sure that I made the request correctly, help with the request? Can you do everything in one request?

Answer 1

something like this

$scores = Matches::find()
->asArray()
->where(['tour_id' => $matche->tour_id, 'part' => $part])
->andWhere(['user1' => $matche->user1, 'user2' => $matche->user2])
->orWhere(['user1' => $matche->user2, 'user2' => $matche->user1])
->select([new Expression('sum(score1) sum_score1'),new Expression('sum(score2) sum_score2')])
->one();

although it is better to take just QueryBuilder for this purpose, and not AR

Answer 2

You can pass yii\db\Expression to sum I have
n't tried it, but in theory you can do it like this:

$exp= new yii\db\Expression('SUM(score1) as score1, SUM(score2) as score2');
$score1 = Matches::find()->sum($exp);

Answer 3

Tell me how else to take data from the table, where the value of part is the maximum?
something like this

$part = TourPlayoff::find()
            ->where(['tour_id' => $this->tour_id, 'user_id' => [$model->user1, $model->user2]])
            ->asArray()
            ->select([new Expression('max(part) part'), new Expression('team_id')])
            ->all();

displays only one record, and there are two of them!