How to build nested query in codeigniter?

2
votes

Hi below is the query to get count of different data from different tables.

select(
        select count(*)
        from teachers
        where teacher_status = 1
  )as teacher_count,
  (
        select count(*)
        from students
        where student_status = 1
  )as students_count,
  (
        select count(*)
        from housekeepers
        where housekeeper_status = 1
  )as housekeeping_count,
  ( 
        select count(*)
        from students
        where student_status = 1 and
              gender = "Male"
  ) as total_male_student_count,
  ( 
        select count(*)
        from students
        where student_status = 1 and
              gender = "Female"
  ) as total_female_student_count

Now i want to build this single query in codeigniter with the help of codeigniter builder class, so can someone guide me please..

Purpose of running single query is to minimize the database hit.

Thanks in advance..!!!

1
phpmysqlcodeigniterdatabase-performance

1 Answers

6
votes

You can use: get_compiled_select like this

$this->db->select('count(*) as count');
$this->db->from('teacher_status');
$teacher_status = $this->db->get_compiled_select();

$this->db->select("select($teacher_status)as teacher_count, ... ");
this->db ...

And use for others.