时间戳

首页 > 解决方案 > 如何在 mysql 中编写对 hasManyThrough 关系的查询？

mysql - 如何在 mysql 中编写对 hasManyThrough 关系的查询？

问题描述

在 Mysql 中为 hasManyThrough 关系编写查询的正确方法是什么？

例如，我有 5 张桌子。它们是a、b、c、d、e。a 通过 b 与 c 有一个 hasMany 关系，a 也通过 d 与 e 有一个 hasMany 关系。

示例的架构

CREATE TABLE `a` (
  `id` varchar(36) NOT NULL,
  `name` tinytext NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `c` (
  `id` varchar(36) NOT NULL,
  `name` varchar(90) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
  `id` int(11) NOT NULL,
  `aId` varchar(36) NOT NULL,
  `cId` varchar(36) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_a_idx` (`aId`),
  KEY `fk_c_idx` (`cId`),
  CONSTRAINT `fk_cId` FOREIGN KEY (`cId`) REFERENCES `c` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aId` FOREIGN KEY (`aId`) REFERENCES `a` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `e` (
  `id` varchar(36) NOT NULL,
  `name` varchar(90) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `d` (
  `id` int(11) NOT NULL,
  `aId` varchar(36) NOT NULL,
  `eId` varchar(36) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_aa_idx` (`aId`),
  KEY `fk_e_idx` (`eId`),
  CONSTRAINT `fk_eId` FOREIGN KEY (`eId`) REFERENCES `e` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_aaId` FOREIGN KEY (`aId`) REFERENCES `a` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT into a(id, name) values ('123', 'name');
insert into c(id, name) values ('1', 'name');
insert into c(id, name) values ('2', 'name2');
insert into c(id, name) values ('3', 'name3');
insert into b(id, aId, cId) values (1, '123', '1');
insert into b(id, aId, cId) values (2, '123', '2');
insert into b(id, aId, cId) values (3, '123', '3');
insert into e(id, name) values ('1', '1name');
insert into d(id, aId, eId) values (1, '123', '1');

和相同的 sqlfiddle

http://sqlfiddle.com/#!9/1e31b7/1

我想使用 1 查询从 a 获取一行数据，并且数据与 c 和 e 中的行相关，这些数据由 b 和 d 连接。

这是我的尝试

select a.*,
    group_concat(lfc.id)
    group_concat(lfc.name)
    group_concat(lfe.id)
    group_concat(lfe.name)
from a,
    left join (
        select c.id, c.name, b.aId
        from b left join c on b.cId=c.id
        where b.aId=123
    ) as lfc on lfc.aId=123
    left join (
        select e.id, e.name, d.aId
        from d left join e on d.eId=e.id
        where d.aId=123
    ) as lfe on lfe.aId=123
where a.id=123 group by a.id

通过使用这个查询，它会导致一个问题。如果 b 中有 2 行，它将使 d 中的数据多一份，这不是我想要的。

group_concat(lfe.id) 字段中的数据应该只包含一个数据，这是数据库中的真实情况，而不是三个。这可以通过使用多个查询轻松完成，但我正在寻找任何可能的方法来仅使用一个查询来获得相同的结果。

有没有人知道如何改进这个查询或任何建议以正确的方式编写查询？

标签： mysql

解决方案

我怀疑这可以将 a.id 留在 WHERE 中以锚定整个查询：

SELECT a.*,
    group_concat(c.id)
    group_concat(c.name)
    group_concat(e.id)
    group_concat(e.name)
FROM a
LEFT JOIN b ON a.id = b.aId
JOIN c ON b.cId = c.id
LEFT JOIN d ON a.id = d.aId
JOIN e ON d.eId = e.id
WHERE a.id=123
GROUP BY a.id

推荐阅读