Left join select using Propel ORM

Question

I have 3 table

major table:

+----+------------+
| id | major      |
+----+------------+
|  1 | Computer   |
|  2 | Architect  |
|  3 | Designer   |
+----+------------+

classroom table:

+----+----------+-------+
| id | major_id | name  | 
+----+----------+-------+
|  1 | 1        | A     |
|  2 | 1        | B     |
|  3 | 1        | C     |
|  4 | 2        | A     |
|  5 | 2        | B     |
|  6 | 3        | A     |
+----+----------+-------+

and finally, student_classroom table

+----+------------+--------------+----------+
| id | student    | classroom_id | status   | 
+----+------------+--------------+----------+
|  1 | John       | 1            | Inactive |
|  2 | Defou      | 2            | Active   |
|  3 | John       | 2            | Active   |
|  4 | Alexa      | 1            | Active   |
|  5 | Nina       | 1            | Active   |
+----+------------+--------------+----------+

how can I use propel to build query below

select 
    a.id,
    a.major,
    b.number_of_student,
    c.number_of_classroom
from major a

left join (
    select
        major.major_id,
        count(student_classroom.id) as number_of_student
    from major
    left join classroom on classroom.major_id = major.id
    left join student_classroom on student_classroom.classroom_id = classroom.id
    where student_classroom.`status` = 'Active'
    group by major_id
) b on b.major_id = a.major_id

left join (
    select
        major.major_id,
        count(classroom.id) as number_of_classroom
    from major
    left join classroom on classroom.major_id = major.id
    group by major_id
) c on c.major_id = a.major_id

Because I want the final result would be something like this, I spend hours trying to figure it out without success.

+----+------------+-------------------+---------------------+
| id | major      | number_of_student | number_of_classroom |
+----+------------+-------------------+---------------------+
|  1 | Computer   | 4                 | 3                   |
|  2 | Architect  | 0                 | 2                   |
|  3 | Designer   | 0                 | 1                   |
+----+------------+-------------------+---------------------+

Answer 1

Try this

select 
    m.id, 
    m.major, 
    count(distinct s.id) as number_of_student , 
    count(distinct c.id) as number_of_classroom 
from major m
left join classroom c on 
    (m.id = c.major_id)
left join student_classroom s 
    on (s.classroom_id = c.id and c.major_id = m.id and s.status = 'active')  
group by m.id
order by m.id