php - 如何为make循环编写左连接

Question

我想用 left join 语句在一个查询中编写此代码。目前我使用 php for 循环这个查询，但它不好而且太慢

该应用程序具有喜欢和不喜欢选项。我想要每个用户的数量，比如

     public function getUserLikedCount($id) {
        $query = $this->db->query(
          "SELECT * FROM `users`, post, `like`
            WHERE users.id = post.post_user_id
            AND like.like_post_id = post.post_id
            AND users.id = ?
            AND post.post_is_active = 1", array($id)
        );
        return $query->num_rows();
    }

谢谢

Answer 1

尝试一下(我不知道你的表like结构):

$sql = 'SELECT u.id AS `user_id`
               COUNT(p.post_id) AS `liked_cnt` <-- or like.id ( if this field exists)
        FROM `post` AS p
        LEFT JOIN `users` AS u
          ON ( u.id = p.post_user_id )
        LEFT JOIN `like` AS l
          ON ( l.like_post_id = p.post_id )  
        WHERE  p.post_is_active = 1
              AND l.status = 1 <-- "liked"
        GROUP BY u.id ';