PHP CodeIgniter 多个 LEFT OUTER 加入查询

Question

我正在使用 PHP CodeIgniter 从我的数据库中进行查询。我需要使用多个 LEFT OUTER 联接。连接使用多个条件(我不确定它是否受错误支持)。对于我要获取的两个参数，我的查询结果具有相同的值:

查询:

$this->db->select('anonymous.*,
    count(items_likes.id) as all_likes, count(items_comments.id) as all_comments');
    $this->db->from('anonymous');
    $this->db->where('anonymous.fid', $feed_id);

    $this->db->group_by('anonymous.id');

    $this->db->join('all_users', 'all_users.id = anonymous.uid', 'left outer');
    $this->db->join('images', 'images.ud = anonymous.uid AND images.fid = anonymous.fid', 'left outer');
    $this->db->join('items_likes', 'items_likes.uid = anonymous.uid AND items_likes.fid = anonymous.fid', 'left outer');

    $this->db->join('items_comments', 'items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid', 'left outer');

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

anonymous:是未注册用户的表

fid:是 feed id

images:是上传到 feed 的所有图片的表格

items_likes:是所有用户对每张图片的点赞

items_comments:是每张图片的所有用户评论

---

问题是 all_comments 总是返回与 all_likes 相同的值。我不确定多个 LEFT-OUTER 连接是否有问题，或者我在每个连接语句中都使用了多个条件，即

items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid

日志结果:

"all_likes":"12","all_comments":"12"

虽然我有12个赞，但评论应该是3个

如果我按如下方式编写查询，结果是可以的:

$this->db->select('anonymous.*,
(SELECT count( items_likes.id ) FROM items_likes WHERE items_likes.uid = anonymous.uid AND items_likes.fid = anonymous.fid) as all_likes, 
(SELECT count( items_comments.id ) FROM items_comments WHERE items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid) as all_comments');
$this->db->from('anonymous');
$this->db->where('anonymous.fid', $feed_id);

$this->db->group_by('anonymous.id');

$this->db->join('all_users', 'all_users.id = anonymous.uid', 'left outer');
$this->db->join('images', 'images.ud = anonymous.uid AND images.fid = anonymous.fid', 'left outer');
//$this->db->join('items_likes', 'items_likes.uid = anonymous.uid AND items_likes.fid = anonymous.fid', 'left outer');

//$this->db->join('items_comments', 'items_comments.uid = anonymous.uid AND items_comments.fid = anonymous.fid', 'left outer');

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

Answer 1

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

需要添加包含NULL的记录

将此 where 子句添加到您的查询中:

$this->db->->where('items_comments.uid IS NULL')    

有关连接的更多信息 here