How to perform count on Yii2 related tables?

Question

MySQL

There are three tables:
user,
region,
project
It is necessary - to pull out an amount of projects in each region.
the user table has a region_id field, the project table has a user_id field.
That is, display a list of all regions and determine the number of each project in it.
How to make count in this case?

Answer 1

1. Create relationships between ActiveRecords of these tables
2. Load data using relations
3. Count the amount of data loaded

Answer 2

Perhaps something like this?

select region.id, count(project.id) as total
from project
left join user on user.id = project.user_id
left join region on region.id = user.region_id
group by region.id