codeigniter 4 select in select query

Question

I'm New to CodeIgniter 4 and I want to write this query with CodeIgniter, How to do that?

SELECT student_id FROM student_rel_class WHERE class_id IN (
    SELECT class_id FROM courses WHERE courses.id IN (
    SELECT course_id FROM homework WHERE homework.id = 41
    ));

thanks.

this is my CodeIgniter Code

$notifications = $stu_rel_class
         ->select('student_rel_class.student_id')
         ->whereIn('student_rel_class.class_id',function (BaseBuilder $builder) use ($data) {
   return $builder->select('courses.class_id')
      ->from('courses')
      ->whereIn('courses.id',function (BaseBuilder $builder) use ($data) {
         return $builder->select('homework.course_id')
             ->from('homework')
             ->where('homework.id',$data['id'])
             ->first();
         });
});

Also I can join tables like this

$notifications = $stu_rel_class
        ->select('student_rel_class.student_id')
        ->join('courses','student_rel_class.class_id = courses.class_id','inner')
        ->join('homework','homework.course_id = courses.id')
        ->where('homework.id',$data['id'])
        ->findAll();

But I not want use join. can anyone help me?

Answer 1

this can be found in Codeigniter Documentation

$db = \Config\Database::connect();

$query = $db->query("SELECT student_id FROM student_rel_class WHERE class_id IN (SELECT class_id FROM courses WHERE courses.id IN (SELECT course_id FROM homework WHERE homework.id = 41")));

$results = $query->getResultArray();

hope this will help solve the problem.