获取所有新闻和所有评论 [英] Fetch all news and all comments

问题描述

我正在尝试编写一个查询，以获取每个新闻的所有新闻和所有评论. 我当前的查询是:

I'm trying to code a query that gets all the news and all the comments for each news. My current query is :

SELECT n.*,
       c.* AS comments
  FROM news n
       JOIN comments c ON (c.news_id = n.id)

但是当我以数组的形式获取查询时，它通过注释为我提供了一个键，而我希望通过新闻和一个子数组中的所有注释来获得一个键.

But when I fetch the query as an array it gives me a key by comment and I would like to have a key by news and all comments in a sub-array.

类似的东西:

Array
(
    [0] => Array
    (
        [id] => 1 // news' id
        ...       // rest of news' data
        [comments] = Array
        (
            [id] => 1 // comment's id
            ...       // rest of comments' data
        )
    ),
    ... // all other news
)

谢谢！

推荐答案

您无法在一个查询中做到这一点-最好采用您已得到的查询并对所得到的答案进行后处理以获得所需的数据结构

You can't do that in one query - best to take the query you've got and post-process the resulting answer to get the data structure you need.

进一步详细说明-任何SQL查询只能返回二维数据数组-一维用于列，一维用于匹配的行.在您的情况下，您实际上想要的是一个三维表.

To elaborate further - any SQL query can only return a two dimensional array of data - one dimension for the columns, and one for the matching rows. In your case what you're actually after is more like a three dimensional table.

还请注意，在撰写查询时，针对每篇文章的每个注释将一遍又一遍地返回所有news数据.这是对数据库服务器带宽和资源的低效率使用.

Note also that on your query as written will return all of the news data over and over again for each comment against each article. That's an inefficient use of bandwidth and resource from the database server.

这样做(使用伪代码)可能更有效:

It's probably more efficient to do it like this (in pseudo-code):

SELECT * FROM news
...
foreach ($rows as $row) {
  $row['comments] = array();
  $news[$row['id']] = $row;
}

SELECT * FROM comments
...
foreach ($rows as $row) {
  $news[$row['news_id']]['comments'][] = $row;
}

第一个查询将获取所有新闻文章并将其放入数组中.第二个查询获取评论，并在每个新闻文章的结构中累积一个单独的数组.

The first query gets all of the news articles and puts them in an array. The second query gets the comments, and accumulates a separate array within each news article's structure.

这篇关于获取所有新闻和所有评论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！