CodeIgniter active record SELECT query to JOIN, GROUP BY, and COUNT() multiple tables

my current query is:

public function teachers_manage() {
    $this->db->select('users.user_id, teacher_id, COUNT(student_id) AS S, COUNT(DISTINCT(users.class)) AS C, schools.region, schools.school_name');      
    $this->db->from('teacher_student_conn');      
    $this->db->join('users', 'teacher_student_conn.student_id=users.user_id','left');
     
    $this->db->join('schools', 'schools.school_id=users.school_id');
    $this->db->where('users.deactivated_at = "0000-00-00 00:00:00" OR users.deactivated_at IS NULL ');
      
    $this->db->where('users.role_id', '1');
    $this->db->group_by("teacher_student_conn.teacher_id");
    $result = $this->db->get();
    return $result->result();
}

It shows me teachers and for each teacher number of classes he teaches and number of students he teaches. I have made join 2 tables - users and teacher_student_conn by users=user_id=teacher_student_conn.student_id and I've put where clause for student not to be deactivated - shows active students. But how to do that also fot the teachers? Another join will change the results. I only want to add where clause for teachers to be active.

My tables look like this:

users

• user_id
• school_id
• class
• role_id
• created_at
• deactivated_at

teacher-student_conn

• id
• teacher_id
• student_id
• created_at

I suggest to add a column for "status" to more easily distinguish the status of student and teacher. And this is my recommended query

SQL Query:

  select 
    from users a
    join teacher-student_conn b on a.user_id = b.student_id
    join schools c on c.school_id = a.school_id
    where (a.status = "Deactivated") and (b.status = "Deactivated")

For codeigniter:

  $this->db->select('a.user_id, b.teacher_id, COUNT(b.student_id) AS S, COUNT(DISTINCT(a.class)) AS C, c.region, c.school_name');
  $this->db->join('teacher_student_conn b', 'b.student_id = a.user_id','left');

  $this->db->join('schools c', 'c.school_id = a.school_id');
  $this->db->where('a.status = "Deactivated" or b.status = "Deactivated" ');

  $this->db->where('a.role_id', '1');
  $this->db->group_by("b.teacher_id");
  $result=$this->db->get("users a");
  return $result->result();