phpcodeigniterselectactiverecordsubquery

Convert SQL with subqueries in the SELECT clause to CodeIgniter active record

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 way?

Solution

  • 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();
    ?>