How to write Subquery in codeigniter active record for this query

Question

 SELECT  from_id, (SELECT COUNT(id) FROM user_messages WHERE from_id=1223 AND status=1) AS sent_unread, 
    (SELECT COUNT(id) FROM user_messages WHERE from_id=1223 AND status=2) AS sent_read 
    FROM user_messages 
    WHERE from_id=1223 
    GROUP BY from_id

How do I write the above select statement in CodeIgniter active record?

This is what I came up with:

  $this->db->select('from_id, (SELECT COUNT(id) FROM user_messages WHERE from_id=1223 AND status=1) AS sent_unread,
        (SELECT COUNT(id) FROM user_messages WHERE from_id=1223 AND status=2) AS sent_read');

    $this->db->where('from_id', $member_id);

    $this->db->group_by('from_id');

    $this->db->from('user_messages');

    $result = $this->db->get();

    //echo $this->db->last_query();
    return $result->row();

Is this the right method?

Answer 1

Try this

<?php
      $query="from_id, (SELECT COUNT(id) FROM user_messages WHERE from_id=1223 AND status=1) AS sent_unread,
            (SELECT COUNT(id) FROM user_messages WHERE from_id=1223 AND status=2) AS sent_read";
      $query_run=$this->db->select($query);
      $query_run->where('from_id', $member_id);
      $query_run->group_by('from_id');

      $result = $query_run->get('user_messages');
      //echo $this->db->last_query();
       return $result->row();
    ?>