php - 如何为此查询在 codeigniter 事件记录中编写子查询

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

如何在 CodeIgniter 事件记录中编写上述 select 语句？

这是我想出的:

  $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();

这是正确的方法吗？

Answer 1

试试这个

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