如何做这个复杂的查询? [英] How to do this complicated query?

问题描述

这是我的数据库架构:

Post:
id
title
body
date

Tag:
id
title

Post_Tag:
id
id_post
id_tag

Comment:
id
id_post
body
date

帖子和标签之间存在多对多的关系.

There is a many to many relationship between post and tag.

我需要在首页上打印最新的10篇文章:

I need to print in homepage this for the latest 10 posts:

<a href="post.php?id=ID_POST">POST_TITLE</a>

POST_BODY

<a href="tag.php?id=ID_TAG_1"> TAG_TITLE_1 </a>
<a href="tag.php?id=ID_TAG_2"> TAG_TITLE_2 </a>
<a href="tag.php?id=ID_TAG_3"> TAG_TITLE_3 </a>

COMMENTS_NUMBER

执行此操作的最佳查询是什么?

What is the best query to do that ?

我已经尝试过了，但是由于每个帖子都有多行，所以效果不佳:

I have tryed this but it doesn't work well because I get multiple rows for each post:

SELECT p.title, p.id, p.date, t.title, t.id, COUNT(c.id)
             FROM post p
             LEFT JOIN post_tag pt 
             ON p.id=pt.id_post
             LEFT JOIN tag t 
             ON t.id=pt.id_tag
             LEFT JOIN comment c
             ON p.id=c.id_post
             GROUP BY p.title, p.id, p.date, t.title
             ORDER BY p.date DESC

推荐答案

您可能不想将其作为单个查询来执行，但从理论上讲您可以.

You probably wouldn't want to do this as a single query, but in theory you could do.

SELECT
    Post.id AS post_id,
    Post.title AS post_title,
    Post.body AS post_body,
    GROUP_CONCAT(CONCAT(Tag.id, "|", Tag.title) SEPARATOR '#') AS tags,
    COUNT(Comment.id) AS comment_count
FROM Post
LEFT JOIN Comment ON Post.id = Comment.id_post
LEFT JOIN Post_Tag ON Post.id = Post_Tag.id_post
LEFT JOIN Tag ON Tag.id = Post_Tag.id_tag
GROUP BY Post.id
ORDER BY Post.date ASC

我没有检查此内容，因为我无权访问您的数据，但应该可以.您需要手动分割标记，标记的格式为"ID | TITLE#ID | TITLE"，但这是唯一需要的额外处理.

I haven't checked this as I don't have access to your data, but it should work. You'll need to manually split the tags, which would appear in the format of "ID|TITLE#ID|TITLE", but that's the only extra processing required.

或者，您可以通过在两个单独的查询之间分配此工作负载来避免使用GROUP_CONCAT标记:

Alternatively, you can avoid the GROUP_CONCAT for tags by splitting this workload between two separate queries:

SELECT
    Post.id AS post_id,
    Post.title AS post_title,
    Post.body AS post_body,
    COUNT(Comment.id) AS comment_count
FROM Post
LEFT JOIN Comment ON Post.id = Comment.id_post
GROUP BY Post.id
ORDER BY Post.date ASC

由此，您将存储所有单独的post_id，并在第二个查询中使用它们，如下所示:

From this, you'd store all of the individual post_ids, and use them in a second query as follows:

SELECT
    Tag.id,
    Tag.title
FROM Post_Tag
INNER JOIN Tag ON Post_Tag.id_tag = Tag.id
WHERE Post_Tag.id_post IN (**comma-separated list of Post IDs**)

这意味着您可以执行两个查询，否则必须执行一个查询以获取所有帖子，然后对每个帖子进行另一个查询以获取标签-这是N + 1查询，而我提出以上建议是解决该问题的常用方法.

This means you can do two queries, where otherwise you'd have to do one to get all of the posts, then another for EACH of those posts to retrieve the tags - THAT is an N+1 query, whereas what I propose above is a common way around the issue.

这篇关于如何做这个复杂的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！