MySQL:具有两个多对多关系和重复项的查询，包含来自子查询的完整数据 [英] MySQL: query with two many to many relations and duplicates, with full data from subqueries

问题描述

这个问题扩展了我刚刚问过的一个问题，在这里刚刚解决: https://stackoverflow.com/posts/62861992/编辑

This question extends one I just asked, and that was just solved here: https://stackoverflow.com/posts/62861992/edit

但是需要一个新的问题，因为另一个问题中遗漏了一个细节.但是首先要注意.

But there is need for a new question, as there is one detail that was missing in the other one. But first things first.

我有四个模型:文章， authors 和 tags .每篇文章可以有很多作者，也可以有很多标签.

I have four models: articles, authors and tags. Each article can have many authors, and also can have many tags.

所以我的数据库将具有以下表格:

So my DB will have the following tables:

article
article_author
author
article_tag
tags

在MySQL中:

DROP TABLE IF EXISTS article_tag;
DROP TABLE IF EXISTS article_author;
DROP TABLE IF EXISTS author;
DROP TABLE IF EXISTS tag;
DROP TABLE IF EXISTS article;

CREATE TABLE IF NOT EXISTS author (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS article (
  id INT(11) NOT NULL AUTO_INCREMENT,
  title VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS tag (
  id INT(11) NOT NULL AUTO_INCREMENT,
  tag VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS article_author (
  article_id INT(11) NOT NULL,
  author_id INT(11) NOT NULL,
  PRIMARY KEY (article_id, author_id),
  INDEX fk_article_author_article_idx (article_id ASC) VISIBLE,
  INDEX fk_article_author_author_idx (author_id ASC) VISIBLE,
  CONSTRAINT fk_article_author_article
    FOREIGN KEY (article_id)
    REFERENCES article (id),
  CONSTRAINT fk_article_author_author
    FOREIGN KEY (author_id)
    REFERENCES author (id)
);

CREATE TABLE IF NOT EXISTS article_tag (
  article_id INT(11) NOT NULL,
  tag_id INT(11) NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  INDEX fk_article_tag_article_idx (article_id ASC) VISIBLE,
  INDEX fk_article_tag_tag_idx (tag_id ASC) VISIBLE,
  CONSTRAINT fk_article_tag_article
    FOREIGN KEY (article_id)
    REFERENCES article (id),
  CONSTRAINT fk_article_tag_tag
    FOREIGN KEY (tag_id)
    REFERENCES tag (id)
);

我们可以在数据库中插入一些数据:

And we can insert some data in our DB:

INSERT INTO article (id, title) VALUES (1, 'first article'), (2, 'second article'), (3, 'third article');
INSERT INTO author (id, name) VALUES (1, 'first author'), (2, 'second author'), (3, 'third author'), (4, 'fourth author');
INSERT INTO tag (id, tag) VALUES (1, 'first tag'), (2, 'second tag'), (3, 'third tag'), (4, 'fourth tag'), (5, 'fifth tag');
INSERT INTO article_tag (article_id, tag_id) VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 4), (2, 5), (3, 1), (3, 2);
INSERT INTO article_author (article_id, author_id) VALUES (1, 1), (1, 2), (1, 3), (2, 2), (2, 4), (3, 1), (3, 2), (3, 3), (3, 4);

现在，在上一个问题中，我正在检索文章，对于每篇文章，我都检索了相关的作者ID和标签ID.提出的解决方案非常优雅:

Now, in the previous question I was retrieving the articles, and for every article I retrieved the related author ids as well as tag ids. The solution proposed is really elegant:

SELECT a.id, a.title,
  (SELECT JSON_ARRAYAGG(aa.author_id)
    FROM article_author aa
    WHERE a.id = aa.article_id
  ) as authors,
    (SELECT JSON_ARRAYAGG(art.tag_id)
    FROM article_tag art
    WHERE a.id = art.article_id
  ) as tags
FROM article a;

但是现在我想检索作者ID以及他们的名字；与标记相同:标记ID和标记本身.

But now I want to retrieve the author ids, as well as their names; the same with the tags: tag id and the tag itself.

显然这不是一个小问题，但是我想知道我有什么选择来实现这一目标.

Apparently this is not a trivial question, but I would like to know what are my options to achieve that.

推荐答案

您可以在子查询中使用 JOIN 获得所需的数据.如果只需要名称，则可以使用:

You can get the data you want using JOINs in the subquery. If you want just the names, you can use:

SELECT a.id, a.title,
       (SELECT JSON_ARRAYAGG(au.name)
        FROM article_author aa JOIN
             author au
             ON au.id = aa.author_id
        WHERE a.id = aa.article_id
       ) as authors,
       (SELECT JSON_ARRAYAGG(t.tag)
        FROM article_tag art JOIN
             tag t
             ON art.tag_id = t.id
        WHERE a.id = art.article_id
       ) as tags
FROM article a;

我不确定ID和名称都需要哪种数据结构.如果您想要一个JSON对象数组，每个对象中都有两个字段:

I'm not sure what data structure you want with both the ids and the names. If you want an array of JSON objects with two fields in each object:

SELECT a.id, a.title,
       (SELECT JSON_ARRAYAGG(JSON_OBJECT('name', au.name, 'id', au.id))
        FROM article_author aa JOIN
             author au
             ON au.id = aa.author_id
        WHERE a.id = aa.article_id
       ) as authors,
       (SELECT JSON_ARRAYAGG(JSON_OBJECT('tag', t.tag, 'id', t.id))
        FROM article_tag art JOIN
             tag t
             ON art.tag_id = t.id
        WHERE a.id = art.article_id
       ) as tags
FROM article a;

此处是此版本的db提琴.

Here is a db<>fiddle for this version.

这篇关于MySQL:具有两个多对多关系和重复项的查询，包含来自子查询的完整数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！