php - 合并 SQL 结果的最佳方式？ 2 个请求与 1 个？

Question

获取带评论的帖子的最佳做法是什么？

        // get posts
        $posts = query('
            SELECT  id,
                    content
            FROM    posts
            WHERE   author_id = 3
        ');

        // get post ids
        $post_ids = [];
        foreach ($posts as $post) {
            $post_ids[] = $post->id;
        }

        //get comment
        $comments = $this->db->query('
                SELECT  id,
                        content
                FROM    comments
                WHERE   post_id IN ('.implode(',',$post_ids ).')
            ');

        //Add to post
        $comments_by_id = [];
        foreach( $comments  as $comment ){
            $comments_by_id[$comment->post_id][] = $comment;    
        }

        //add comment to post
        foreach ($posts as &$post) {
            if (isset($comments_by_id[$post->id])){
                $post->comments = $comments_by_id[$post->id];
            }
        }

我也可以使用 group_concat 通过 1 个查询来实现这一点。

SELECT p.id, p.content, GROUP_CONCAT(c.content)
FROM posts p
INNER JOIN comments c ON c.post_id = p.id
WHERE p.author_id = 3
GROUP BY p.id, p.content

最终结果是 $posts 包含 id, content, comments (in array)

哪个更好？有没有更好的办法？

Answer 1

在大多数情况下，当涉及到操作结构化数据时，让 RDBMS 来完成繁重的工作会更有效。

您似乎正在寻找 posts 和 comments 之间的简单 JOIN。 FROM 子句应如下所示:

FROM posts p
INNER JOIN comments c ON c.post_id = p.id

在加入后你想如何安排数据集是相当不清楚的，但既然你提到了 GROUP_CONCAT()，这里是一个聚合查询，它将为每个帖子返回一条记录，所有相应的连接的评论(使用 , 分隔符):

SELECT p.id, p.content, GROUP_CONCAT(c.content)
FROM posts p
INNER JOIN comments c ON c.post_id = p.id
WHERE p.author_id = 3
GROUP BY p.id, p.content