mysql - 如何在mysql中编写hasM​​anyThrough关系的查询？

Question

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

例如，我有 5 张 table 。它们是a、b、c、d、e。 a 与 c 到 b 存在 hasMany 关系，a 也与 e 到 d 存在 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中的行相关，而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)中的数据应该只包含一个数据，这是数据库中的真实情况，而不是三个。这可以通过使用多个查询轻松完成，但我正在寻找任何可能的方法来仅使用一个查询来获得相同的结果。

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

Answer 1

我怀疑这可以将 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