使用SQL查询获取所有匹配行

Question

我有带有以下数据的posts表/structure：

posts_id   post_message   u_id  like_count  created
===================================================
1         content.......  1     25          1559041633
2         content.......  2     25          1559041633

和具有以下数据/结构的posts_like表：

like_id    posts_id   u_id   r_id    created
=============================================
22         2          1      2       1559041633
27         2          2      4       1559041633
30         1          2      7       1559041633

注： u_id表示用户id，r_id表示反应id，posts_id表示posts id。

现在，我希望从posts表中获得所有的帖子，以及users表中的全名，以及每个post下的 all u_id和r_id。

例如，您可以看到在posts_id = 2下，我有u_id 1和2以及 r_id 2和4。

愿望输出：

first post content  
user full Name
u_id: 2
r_id: 7

second post content  
user full Name
u_id: 1, 2
r_id: 2, 4

当前我正在使用这个SQL查询

$query = $this->_db->_pdo->prepare("
SELECT p.posts_id, p.post_message, p.like_count, p.created, u.full_name,
 u.u_id, pl.r_id FROM posts AS p LEFT JOIN users AS u ON u.u_id = p.u_id 
LEFT JOIN posts_like AS pl ON pl.posts_id = p.posts_id GROUP BY 
p.posts_id ORDER BY p.posts_id DESC ");  

上面的查询没有在每个帖子下获得all u_id和r_id。

Answer 1

您需要在您的GROUP_CONCAT和r_id字段中使用一个u_id来获得所有反应的列表。请注意，您需要使用pl.u_id，而不是u.u_id，因为u.u_id指的是post创建者，而不是对帖子作出反应的人。

SELECT p.posts_id, p.post_message, p.like_count, p.created, 
       u.full_name,
       GROUP_CONCAT(pl.u_id ORDER BY pl.u_id) AS u_id,
       GROUP_CONCAT(pl.r_id ORDER BY pl.u_id) AS r_id
FROM posts AS p 
LEFT JOIN users AS u ON u.u_id = p.u_id 
LEFT JOIN posts_like AS pl ON pl.posts_id = p.posts_id 
GROUP BY p.posts_id
ORDER BY p.posts_id DESC

输出：

posts_id    post_message    like_count  created     full_name   u_id    r_id
2           content.......  25          1559041633  Mary Brown  1,2     2,4
1           content.......  25          1559041633  John Smith  2       7

dbfiddle演示