我有一个非常恼人的问题,我已经签约工作的应用程序。
数据库结构,,我不能修改兼容性的原因是一个烂摊子。这基本上是一种多对多对多对多对多的关系。它是这样的:
与
---------------------------
| contact_id | name | ... |
---------------------------
| 1 | foo | |
---------------------------
metadefinition
-----------------------------
| metadefinition_id | name |
-----------------------------
| 1 | title |
-----------------------------
| 2 | job |
-----------------------------
contact_metadata
----------------------------
| contact_id | metadata_id |
----------------------------
| 1 | 1 |
----------------------------
| 1 | 2 |
----------------------------
-------------------------------------------
| metadata_id | metadefinition_id | value |
-------------------------------------------
| 1 | 1 | mrs |
-------------------------------------------
| 2 | 2 | coder |
-------------------------------------------
因此,对于单个联系人,我想搜索所有这些并得到这样的内容:
-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1 | foo | mrs | coder |
-----------------------------------------------------
到目前为止我所做的。我可以提前获取metadefinition
的列表,这不是问题。所以我建立了这样一个查询:
SELECT contact.*,m1.value AS `metadata.title` FROM contact
LEFT JOIN contact_metadata ON contact.contact_id = contact_metadata.contact_id
LEFT JOIN metadata m1 ON contact_metadata.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
GROUP BY contact_id
这适用于单个metadefinition
,我得到这样的东西:
--------------------------------------
| contact_id | name | metadata.title |
--------------------------------------
| 1 | foo | mrs |
--------------------------------------
如果我尝试使用两个,但是:
SELECT contact.*,m1.value AS `metadata.title`,m2.value AS `metadata.job` FROM contact
LEFT JOIN contact_metadata ON contact.contact_id = contact_metadata.contact_id
LEFT JOIN metadata m1 ON contact_metadata.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
LEFT JOIN metadata m2 ON contact_metadata.metadata_id = m2.metadata_id AND m2.metadefinition_id = 2
GROUP BY contact_id
我:
-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1 | foo | mrs | NULL |
-----------------------------------------------------
如果我删除GROUP BY子句,当然,我得到:
-----------------------------------------------------
| contact_id | name | metadata.title | metadata.job |
-----------------------------------------------------
| 1 | foo | mrs | NULL |
-----------------------------------------------------
| 1 | foo | NULL | coder |
-----------------------------------------------------
只要查询时间是相对可接受的,我是开放的(考虑到结构,如果100000条记录需要10秒,总比没有好)
它是临时表还是存储过程,我不在乎,只要我不需要改变实际的数据库结构。
相信我,当需要重新装修的时候,我很乐意把这整个东西拆掉。
是否有解决方案,是否可能?
我将使用一对相关子查询。就像
SELECT c.*
,( Select value from metadata m1
INNER JOIN contact_metadata cm
on cm.metadata_id = m1.metadata_id
WHERE m1.metadefinition_id = 1
AND cm.contact_id = c.contact_id )AS `metadata.title`
,( Select value from metadata m1
INNER JOIN contact_metadata cm
ON cm.metadata_id = m1.metadata_id
WHERE m1.metadefinition_id = 2
AND cm.contact_id = c.contact_id )AS `metadata.job`
FROM contact c
WHERE c.contact_id = 1
您需要将每个元数据项(title, job等)作为单独的子查询获取,然后在contact_id上连接以将每个联系人的这些元数据项聚集在一起。这样的:
SELECT contact.*, metadata_title, metadata_job FROM contact
INNER JOIN
(SELECT contact_id,m.value AS `metadata_title` FROM contact_metadata
LEFT JOIN metadata m ON contact_metadata.metadata_id = m.metadata_id AND m.metadefinition_id = 1) metaTitle ON metaTitle.contact_id=contact.contact_id
INNER JOIN
(SELECT contact_id,m.value AS `metadata_job` FROM contact_metadata
LEFT JOIN metadata m ON contact_metadata.metadata_id = m.metadata_id AND m.metadefinition_id = 2) metaJob ON (metaJob.contact_id=contact.contact_id)
如果您想允许客户端没有特定元数据项的可能性,请使用LEFT JOINs
代替,因为目前,查询将只返回定义了job和title的客户端
现在我终于正确地阅读了这个问题,这里是我的两点意见,不管它值多少钱(没有双关语)。
SELECT contact.contact_id, contact.name, m1.value AS 'metadata.title', m2.value AS 'metadata.job'
FROM dbo.contact
INNER JOIN dbo.contact_metadata cm1 ON cm1.contact_id = cm1.contact_id
INNER JOIN dbo.metadata m1 ON cm1.metadata_id = m1.metadata_id AND m1.metadefinition_id = 1
INNER JOIN dbo.contact_metadata cm2 ON contact.contact_id = cm2.contact_id
INNER JOIN dbo.metadata m2 ON cm2.metadata_id = m2.metadata_id AND m2.metadefinition_id = 2