选择分层配置

Question

编辑：很抱歉造成混淆。刚刚得到老板的同意，可以发布这部分模式。如果我被允许发布一张图片，我会在最初的帖子中有更多的细节。

我有一个配置模式，看起来像这样：

http://img717.imageshack.us/img717/7297/heirarchy.png

每个级别都包含在其下的级别中（即-合作伙伴有多个程序），每个配置级别与其他类型的配置级别共享配置键（即-可以在合作伙伴级别设置默认时区，然后从程序、投资组合或设备级别覆盖）。

这允许我们为一种类型的对象设置一个默认值，然后用更具体的分类法覆盖它。例如：

假设我有一个合作伙伴对象是一家公司。假设hierarchy_configuration_key 1是默认时区。我放了一个合作伙伴配置，上面写着，大多数情况下，该合作伙伴将位于东海岸（纽约时间）。

现在我有多个合作伙伴支持的项目。假设这个特定的项目是以加利福尼亚州为基地的。我放了一个程序配置，上面写着该程序中的设备是萨克拉门托时间。

现在，让我们跳过投资组合，说有人在加州以外的地方注册了这个项目，搬到了丹佛，但仍然是一个客户。我们设置了一个设备配置，显示它们现在处于山地时间。

层次结构如下：

Level     |Timezone (hierarchy_configuration_key 1)
---------------------------------------------------
Partner   |NYC
Program   |Sacramento
Portfolio |null (defaults to most granular above it, so Sacramento)
Device    |Denver

现在我想选择按hierarchy_configuration_key_id:分组的配置

我可以使用内部联接来遍历级别，但我希望通过选择来为设备的主键（device_id）提供这样的结果（按hierarchy_configuration_key_id分组）：

device_id |portfolio_id |program_id |partner_id |device_config |portfolio_config |program_config| partner_config
---------------------------------------------------------------------------------------------------------------------
1         |2            |1          |35         |Denver        |null             |Sacramento    | NYC

同样可以接受的是一个选择，它只给了我最相关的配置值，即：

device_id |portfolio_id |program_id |partner_id |config_value
-------------------------------------------------------------
1         |2            |1          |35         |Denver      

提前谢谢。如果你需要更多的澄清，请告诉我。

Answer 1

我认为@EugenRieck的评论指出了这里唯一不起作用的部分
-Which field tells the Miata it is a Child of Mazda?

我会稍微改变一下结构。。。

ENTITY_TABLE
entity_id | parent_entity_id | entity_name
     1            NULL         Vehicle
     2             1           Car
     3             2           Mazda
     4             3           Miata
     5             1           Cycle
     6             5           Unicycle
     7             6           Broken Unicycle

PROPERTY_TABLE
entity_id | property_type | value
     1          Wheels        4
     2          Wheels        NULL
     3          Wheels        NULL
     4          Wheels        NULL
     5          Wheels        2
     6          Wheels        1
     7          Wheels        0
     (And repeated for other property types as appropriate)

-- Every entity must have the same properties as the parents
-- (otherwise you have to find the topmost parent first to know what properties exist)
-- An entity may only have 1 parent
-- The topmost parent must have a NULL parent_id
-- The bottommost parent must be no more than 3 joins away from the topmost parent

然后你可以吃这样的东西。。。

SELECT
  entity1.id,
  property1.property_type,
  entity1.name,
  entity2.name,
  entity3.name,
  entity4.name,
  property1.value,
  property2.value,
  property3.value,
  property4.value,
  COALESCE(property1.value, property2.value, property3.value, property4.value) AS inherited_value
FROM
  entity               AS entity1
LEFT JOIN
  entity               AS entity2
    ON entity2.id = entity1.parent_id
LEFT JOIN
  entity               AS entity3
    ON entity3.id = entity2.parent_id
LEFT JOIN
  entity               AS entity4
    ON entity4.id = entity3.parent_id
INNER JOIN
  property             AS property1
    ON property1.entity_id = entity1.id
LEFT JOIN
  property             AS property2
    ON  property2.entity_id     = entity2.id
    AND property2.property_type = property1.property_type
LEFT JOIN
  property             AS property3
    ON  property3.entity_id     = entity3.id
    AND property3.property_type = property1.property_type
LEFT JOIN
  property             AS property4
    ON  property4.entity_id     = entity4.id
    AND property4.property_type = property1.property_type
WHERE
    entity1.id              = @entity_id
AND property1.property_type = @property_type

Answer 2

此解决方案基于您的架构，@param1是hierarchy_configuration_key_id，@param2是所需的设备_id。它使用了一种类似于民主党的方法，尽管它是独立得出的，只是我借用了COALESCE。

SELECT *,
IF(dv_key IS NOT NULL,'device',IF(pf_key IS NOT NULL,'portfolio',IF(pg_key IS NOT NULL,'program',IF(pt_key IS NOT NULL,'partner',NULL)))) AS hierarchy_level,
COALESCE(dv_key,pf_key,pg_key,pt_key) AS key_id,
COALESCE(dv_value,pf_value,pg_value,pt_value) AS value
FROM
(SELECT sim_id,
dv.device_id, pt.partner_id, pg.program_id, pf.portfolio_id,
dvc.hierarchy_configuration_key_id AS dv_key, dvc.configuration_value AS dv_value,
pfc.hierarchy_configuration_key_id AS pf_key, pfc.configuration_value AS pf_value,
pgc.hierarchy_configuration_key_id AS pg_key, pgc.configuration_value AS pg_value,
ptc.hierarchy_configuration_key_id AS pt_key, ptc.configuration_value AS pt_value
FROM device dv
LEFT JOIN portfolio pf USING(portfolio_id)
LEFT JOIN program pg USING(program_id)
LEFT JOIN partner pt USING(partner_id)
LEFT JOIN device_configuration dvc ON dv.device_id=dvc.device_id AND dvc.hierarchy_configuration_key_id=@param2 AND dvc.active='true'
LEFT JOIN portfolio_configuration pfc ON pf.portfolio_id=pfc.portfolio_id AND pfc.hierarchy_configuration_key_id=@param2 AND pfc.active='true'
LEFT JOIN program_configuration pgc ON pg.program_id=pgc.program_id AND pgc.hierarchy_configuration_key_id=@param2 AND pgc.active='true'
LEFT JOIN partner_configuration ptc ON pt.partner_id=ptc.partner_id AND ptc.hierarchy_configuration_key_id=@param2 AND ptc.active='true'
WHERE dv.device_id = @param1) hierchy;