CODEWITHSUNDEEP
sqlyii2
Cristian
Cristian

Reputation: 23

Left join 2 tables in yii2

with 3 tables classes(id, name), student_classes(id, class_id, student_id, status) and teacher_classes(id, class_id, teacher_id, status). status received value in [0,1]; I want to know all info of class and number of students and teachers in class which have status = 1. So i try

$query = (new \yii\db\Query())
    ->select(['cl.id as cl_id', 'cl.name as clname', 'count(distinct sc.student_id as stdnumbers)', 'count(distinct teacher_id) as tcnumbers'])
    ->from('classes as cl')
    ->leftJoin('student_classes as sc', 'cl.id = sc.class_id')
    ->leftJoin('teacher_classes as tc', 'cl.id = tc.class_id')
    ->where(['status => 1'])->groupBy(['cl_id']);

$dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort' => false,
]);

Without where(['status => 1']) i have all class, but number of students and teachers is not true because i get records with status = 0, with where i have true number, but not all classes because classes which dont have student_classes and teacher_classes is not get out. example

classes(id, name) = [
    {1, 'Alpha'},
    {2, 'Beta'},
    {3, 'Gamma'}
];
student_classes(id, class_id, student_id, status) = [
    {1, 1, 1, 0},
    {2, 1, 2, 1},
    {3, 1, 3, 1}
];
teacher_classes(id, class_id, teacher_id, status) = [
    {1, 2, 1, 1},
    {2, 1, 1, 1}
];

I want query to return like this

query (cl_id, cl_name, stdnumbers, tcnumbers) = [
    {1, 'Alpha', 2, 1},
    {2, 'Beta', 0, 1},
    {3, 'Gamma', 0, 0}
]

What i can do? Thank you.

Upvotes: 2

Views: 76

Answers (1)

Ajul Maharjan
Ajul Maharjan

Reputation: 44

select * from 
classes, student_classes
where classes.id = student_classes.id
and student_classes.status = 1

Upvotes: 1

Related Questions