一到多个，在一个查询中选择一个和多个

I have four tables

我有四个表

POST -- USER --IMAGE-- COMMENT

POST——USER——IMAGE——COMMENT

I want to retrieve the information about all the posts, the users who posted them, the image associated with it (each post has only one image) and the (list/array) comments associated with every given post.

我想检索关于所有贴子的信息，发布贴子的用户，与贴子相关的图片(每个贴子只有一个图片)，以及与每个贴子相关的(列表/数组)注释。

This is what I have :

这就是我所拥有的:

SELECT * 
FROM post 
LEFT JOIN image 
    ON post.image_id=image.id 
LEFT JOIN user 
    ON user.id=post.user_id 
LEFT JOIN comment 
    ON comment.post_id =post.id

This almost works, except that each post record is is repeated for each of its many comments. How do I get one record with all the comments in it ?

这几乎是可行的，除了每个post记录在它的许多评论中被重复。我如何得到一个记录和所有的评论?

2 个解决方案

#1

Except that the * operator is really inefficient, you just can't do in only one query "one post"<=>"many comment". Every entry will merge the post and its comment.

除了*操作符非常低效之外，您不能只在一个查询中执行“一个post”<=>“许多注释”。每个条目都会合并文章和评论。

You can use a trick, like GROUP_CONCAT to create a "row of comments". But it will be really weird.

您可以使用GROUP_CONCAT之类的技巧来创建“注释行”。但这真的很奇怪。

An example for Group_Concat

一个例子为Group_Concat

SELECT p.*, 
   GROUP_CONCAT(image.url SEPARATOR "~") AS image_url, GROUP_CONCAT(comment.text SEPARATOR "#~#") AS comment_text
   FROM post p 
LEFT JOIN image 
    ON p.image_id=image.id 
LEFT JOIN user 
    ON user.id=p.user_id 
LEFT JOIN comment 
   ON comment.post_id =p.id

it will give you one line per post with each comment glued one to another by a "#~#". Not so cool.

它会给你每篇文章写一行，每个评论用“#~#”粘在一起。不那么酷。

I think you just have to let it be like SQL handles it naturally.

我认为您必须让它像SQL那样自然地处理它。

#2

This seems like a job for more than one query. You could select the images, then for each one select the comments, or you could select the images, then select all comments and group them as necessary in code. The choice between those depends on how you're using it. But if you want to adhere to relational database design patterns, you'll want to do this as more than one query. You can't return an array like you're looking for.

这似乎是一个用于多个查询的任务。您可以选择图像，然后为每个图像选择注释，或者您可以选择图像，然后选择所有注释，并根据需要在代码中对它们进行分组。两者之间的选择取决于你如何使用它。但是，如果您希望遵循关系数据库设计模式，您将希望将其作为多个查询执行。你不能像你正在寻找的那样返回一个数组。

#1