How to write a query with ActiveRecord in Yii2?

Question

Yii

How to write a query using ActiveRecord.
There is a user table, it has a date_contract field. You need to get the number of users by month.
It's funny, but so far only such an option stubbornly climbs into my head

$t1 = User::find()->where(['between', 'date_contract', '2017-01-01', '2017-01-31'])->count();
$t2 = User::find()->where(['between', 'date_contract', '2017-02-01', '2017-02-28'])->count();
$t3 = User::find()->where(['between', 'date_contract', '2017-03-01', '2017-03-31'])->count();
$t4 = User::find()->where(['between', 'date_contract', '2017-04-01', '2017-04-30'])->count();
$t5 = User::find()->where(['between', 'date_contract', '2017-05-01', '2017-05-31'])->count();
$t6 = User::find()->where(['between', 'date_contract', '2017-06-01', '2017-06-30'])->count();

6 different queries, 6 different variables, how to properly fit all this into one query and get one array?

Answer 1

First you need to understand what kind of query you need to make in the database in order to get the data, then make it under Yii2. You can start with this

SELECT
    LEFT(`date_contract`, 0, 7) as `month`,
    COUNT(`id`) as `count`
FROM `users`
GROUP BY `month`