文章和评论在一个查询 - Articles and comments in one query

Articles and comments in one query

本文关键字：一个查询评论文章 | 更新日期: 2023-09-26

假设我有两个表:

文章

(正如articleDetail)

评论> (commentId、commentDetail commentArticle)
我用commentArticle = articleId
链接文章和评论表

让我们说，我用这个查询获得文章;

SELECT * FROM articles ORDER BY articleId DESC

结果是;

Array(
    [0] => stdClass Object
        (
            [articleId] => 3
            [articleDetail] => The details of article 3
        )
    [1] => stdClass Object
        (
            [articleId] => 2
            [articleDetail] => The details of article 2
        )
    [2] => stdClass Object
        (
            [articleId] => 1
            [articleDetail] => The details of article 1
        )
    ...
)

你知道，像往常一样;)

我想只用一个查询获得文章和评论。我试过这个;

SELECT 
    articles.*,
    comments.*
FROM 
    articles
LEFT JOIN
    comments ON comments.commentArticle = articles.articleId
ORDER BY
    articles.articleId
DESC

不!它不工作。我想用一个查询获得评论和文章，就像这样;

Array(
    [0] => stdClass Object
        (
            [articleId] => 3
            [articleDetail] => The details of article 3
            [articleComments] => Array(
                                [0] => stdClass Object
                                       (
                                            [commentId] => 1
                                            [commentDetail] => Details of comment 1
                                            [commentArticle] => 3
                                       )
                                [1] => stdClass Object
                                       (
                                            [commentId] => 2
                                            [commentDetail] => Details of comment 2
                                            [commentArticle] => 3
                                       )
                                [2] => stdClass Object
                                       (
                                            [commentId] => 3
                                            [commentDetail] => Details of comment 3
                                            [commentArticle] => 3
                                       )
                          )
        )
    [1] => stdClass Object
        (
            [articleId] => 2
            [articleDetail] => The details of article 2
            [articleComments] => Array( )
        )
    [2] => stdClass Object
        (
            [articleId] => 1
            [articleDetail] => The details of article 1
            [articleComments] => Array(
                                [0] => stdClass Object
                                       (
                                            [commentId] => 4
                                            [commentDetail] => Details of comment 4
                                            [commentArticle] => 1
                                       )
                                [1] => stdClass Object
                                       (
                                            [commentId] => 1
                                            [commentDetail] => Details of comment 5
                                            [commentArticle] => 3
                                       )
                          )
        )
    ...
)

是否有办法做到这一点与一个单一的查询?甚至，重新排序评论?

谢谢。

您可以选择的唯一方法是，总是在每个评论中包含文章信息。没有办法做你想做的事。您将始终得到具有相同列数的行

对于Postgres，您可以通过使用数组来实现:

SELECT 
       articles.*,
       array(select comments.single_column 
                    -- or (comments.column1 || '|' || commnets.column2) 
                    -- where '|' is any column separator you wanna use
               from comments 
              where comments.commentArticle = articles.articleId
            ) as comments_array
FROM 
       articles
ORDER BY
       articles.articleId DESC

不确定其他数据库(如SQLite/Oracle/等)是否支持这种实现

我能想到的最接近的东西是:

select
  a.articleId,
  articleDetail,
  concat('[', group_concat(
    concat('["commentId"=>"', commentId,
      '", commentDetail=>"', commentDetail,
      '", "commentArticle"=>"', commentArticle, '"]')), ']') comment_array 
from articles a
left join comments c on c.commentArticle = a.articleId
group by 1, 2
order by a.articleId desc

这将返回一个字符串版本的PHP数组数组的注释作为一个单独的值。这取决于您的应用程序将其转换为实际数组的实际数组。