How to write such a SQL query as an ActiveRecord?

Question

MySQL

I have this SQL query:

update users u_
join (
    select m.user_id id, u.name name, SUM(IF(m.`status` = 'new' or m.`status` = 'newest', 1, 0)) new_marks_total
    from marks m
    left join users u on u.id = m.user_id
    where m.`status` in ('oldest', 'old', 'new', 'newest') and m.user_id is not null and u.name is not null
    group by m.user_id
) t1 on t1.id = u_.id
set u_.marks_total = t1.new_marks_total
where u_.id = <USER_ID>;

Its essence is that according to the user ID, we count the number of all marks with the statuses new and newest in the marks table and add them to the marks_total field in the user table.
How to make such SQL in YII2 not using createCommand(), but using ActiveRecord?

Answer 1

Why write this on AR?
Do you want to get the user model after the update?

Answer 2

1. Completely on ActiveRecord will not work, because it is only for ANSI SQL, and your update of two tables will only work in MySQL.
You can partially:

echo Mark::find()
->select(['user_id', 'name', 'new_marks_total' => new Expression('...')])
->with('user')
->where(['status' => ['oldest', 'old', 'new', 'newest']])
->andWhere(['IS NOT', 'user_id', null])
->andWhere(['IS NOT', 'name', null])
->groupBy('user_id')
->createCommand()
->rawSQL;

2. But it's still shitty code. Because at group by it is possible in select only this grouped field or group functions. For example, there is a user with two mark. When grouping by user.id, which of the two marks should be in m.status?