How to build nested query in codeigniter?

Question

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..!!!

aya · Accepted Answer · 2019-02-12 11:14:45Z

7

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.

edited Feb 12, 2019 at 11:14

answered Jan 7, 2017 at 12:21

aya

1,6134 gold badges32 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rostamiani Over a year ago

the second select should be in a double quote not single quote.

Collectives™ on Stack Overflow

How to build nested query in codeigniter?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related