我已经搜索过这个问题并在过去的几天里试图自己解决,但我一直无法做到。在我的搜索中,最接近的答案是Stack Overflow上的这个回答:EAV Select query from spreaded value tables。
因此,我转向了互联网寻求帮助!
我有一个使用 EAV(实体属性值)模型的数据库。但这里有个问题:实际实体与其他 EAV 表并没有直接连接。让我更具体地说:假设有一个人和一个站点表,并且它们只有它们的主键:
由于这些实体(即 Person 和 Site)的属性(在我的架构中称为“属性”)必须是动态的,因此它们都必须存储在各自表格之外,即 EAV 表格中。以下是数据库架构的 EAV 部分(如果您有任何建议,请告诉我是否完全正确)。-- http://i.stack.imgur.com/EN3dy.png 架构的 EAV 部分基本上有以下表格:
-
--------------------------------------- |entity_table_id | entity_table_name | | 1 | person | | 2 | site | | . | . | | . | . | ---------------------------------------
让我给你一个数据库的可能实例,以便更清楚地理解:
人员表 ------------- | 人员ID | | 1 | | 2 | -------------
站点表 ----------- | 站点ID | | 1 | | 2 | -----------
实体表 --------------------------------------- | 实体表ID | 实体表名称 | | 1 | 人员 | | 2 | 站点 | ---------------------------------------
属性表 ------------------------------------- | 属性ID | 属性代码 | | 1 | PERSON_FIRST_NAME | | 2 | PERSON_LAST_NAME | | 3 | PERSON_BIRTH_DATE | | 4 | SITE_NAME | | 5 | SITE_PHONE_NR_1 | | 6 | SITE_PHONE_NR_2 | | 7 | SITE_LATITUDE | | 8 | SITE_LONGITUDE | | 9 | SITE_CITY | | 10 | SITE_COUNTRY | | 11 | SITE_ZIP_CODE | -------------------------------------
字符类型属性值表 ----------------------------------------------------------------------------------------- | 属性值ID | 属性ID | 实体表ID | 实体对象ID | 属性值 | | 1 | 1 | 1 | 1 | Richard| | 2 | 2 | 1 | 1 | Hammer | | 3 | 1 | 1 | 2 | Bruce | | 4 | 2 | 1 | 2 | Heaton | | 5 | 4 | 2 | 1 | BatCave| | 6 | 5 | 2 | 1 |+49123456789 | | 7 | 4 | 2 | 2 |BigCompany | | 8 | 5 | 2 | 2 | 987654321 | | 9 | 6 | 2 | 2 | 147852369 | | 10 | 9 | 2 | 2 | Berlin | | 11 | 10 | 2 | 2 | Germany | | 12 | 11 | 2 | 2 | 14167 | -----------------------------------------------------------------------------------------
日期时间类型属性值表 ----------------------------------------------------------------------------------------- | 属性值ID | 属性ID | 实体表ID | 实体对象ID | 属性值 | | 1 | 3 | 1 | 1 |1985-05-31| -----------------------------------------------------------------------------------------
数字类型属性值表 ----------------------------------------------------------------------------------------- | 属性值ID | 属性ID | 实体表ID | 实体对象ID | 属性值 | | 1 | 7 | 2 | 1 | 1.402636 | | 2 | 8 | 2 | 1 | 7.273922 | -----------------------------------------------------------------------------------------
(属性值文本和属性值布尔表为空)
正如您所见,每个实体的对象并不一定具有相同的属性(属性)。域真的很松散。
因此,像之前许多人一样,我不确定如何以可读的方式检索所有这些信息,即如何获取有关Person表或Site表记录的所有信息?
换句话说,我该如何获得类似于下面这样的东西:
Person表查看 ---------------------------------------------------- | Person ID | 属性代码 | 属性值 | | 1 | PERSON_FIRST_NAME | Richard | | 1 | PERSON_LAST_NAME | Hammer | | 1 | PERSON_BIRTH_DATE | 1985-05-31| | 2 | PERSON_FIRST_NAME | Bruce | | 2 | PERSON_LAST_NAME | Heaton | ----------------------------------------------------
Site表查看 -------------------------------------------------------- | Site ID | 属性代码 | 属性值 | | 1 | SITE_NAME | Batcave | | 1 | SITE_PHONE_NR_1 | +49123456789 | | 1 | SITE_LATITUDE | 1.402636 | | 1 | SITE_LONGITUDE | 7.273922 | | 2 | SITE_NAME | BigCompany | | 2 | SITE_PHONE_NR_1 | 987654321 | | 2 | SITE_PHONE_NR_2 | 147852369 | | 2 | SITE_CITY | Berlin | | 2 | SITE_COUNTRY | Germany | | 2 | SITE_ZIP_CODE | 14167 | --------------------------------------------------------
或者,如果更容易,可以像这样:
Person表查看
------------------------------------------------------------------------ | Person ID | PERSON_FIRST_NAME | PERSON_LAST_NAME | PERSON_BIRTH_DATE | | 1 | Richard | Hammer | 1985-05-31 | | 2 | Bruce | Heaton | | ------------------------------------------------------------------------
Site表查看
---------------------------------------------------------------------------------------------------------------------------------------- | Site ID | SITE_NAME | SITE_PHONE_NR_1 | SITE_PHONE_NR_2 | SITE_LATITUDE | SITE_LONGITUDE | SITE_CITY | SITE_COUNTRY | SITE_ZIP_CODE | | 1 | Batcave | +49123456789 | | 1.402636 | 7.273922 | | | | | 2 | BigCompany | 987654321 | 147852369 | | | Berlin | Germany | 14167 | ----------------------------------------------------------------------------------------------------------------------------------------
我意识到这可能非常令人困惑。请告诉我您还需要哪些信息或某些部分的更好解释,以便我可以更好地帮助您。
我不认为只需要一个SQL查询(每个实体)就够了。我知道很可能需要多于一个查询,而且这些查询很可能需要由PHP之类的语言进行“组装”,以使其真正动态化。所以即使有人能够仅仅解释一下我如何获取上述虚构属性的所有信息,我也会非常感激!
感谢您的任何帮助!
因此,我转向了互联网寻求帮助!
我有一个使用 EAV(实体属性值)模型的数据库。但这里有个问题:实际实体与其他 EAV 表并没有直接连接。让我更具体地说:假设有一个人和一个站点表,并且它们只有它们的主键:
person_id
和 site_id
。由于这些实体(即 Person 和 Site)的属性(在我的架构中称为“属性”)必须是动态的,因此它们都必须存储在各自表格之外,即 EAV 表格中。以下是数据库架构的 EAV 部分(如果您有任何建议,请告诉我是否完全正确)。-- http://i.stack.imgur.com/EN3dy.png 架构的 EAV 部分基本上有以下表格:
-
property
- property_value_varchar
- property_value_text
- property_value_number
- property_value_boolean
- property_value_datetime
- entity_tables
所以,由于实体与 EAV 部分并不是“直接连接”的,我使用entity_tables
表作为实际表的参考。因此,对于上面的例子,entity_tables
表应该看起来像这样:--------------------------------------- |entity_table_id | entity_table_name | | 1 | person | | 2 | site | | . | . | | . | . | ---------------------------------------
property
表格实际上保存任何实体可以拥有的不同属性,例如“PERSON_FIRST_NAME”或“LOCATION_NAME”等。
property_value_*
表格除了 property_value
的数据类型外,都是完全相同的。这些表格保存每个实体对象的属性的实际值,这些实体对象通过entity_table_id
和 entity_object_id
进行映射。让我给你一个数据库的可能实例,以便更清楚地理解:
人员表 ------------- | 人员ID | | 1 | | 2 | -------------
站点表 ----------- | 站点ID | | 1 | | 2 | -----------
实体表 --------------------------------------- | 实体表ID | 实体表名称 | | 1 | 人员 | | 2 | 站点 | ---------------------------------------
属性表 ------------------------------------- | 属性ID | 属性代码 | | 1 | PERSON_FIRST_NAME | | 2 | PERSON_LAST_NAME | | 3 | PERSON_BIRTH_DATE | | 4 | SITE_NAME | | 5 | SITE_PHONE_NR_1 | | 6 | SITE_PHONE_NR_2 | | 7 | SITE_LATITUDE | | 8 | SITE_LONGITUDE | | 9 | SITE_CITY | | 10 | SITE_COUNTRY | | 11 | SITE_ZIP_CODE | -------------------------------------
字符类型属性值表 ----------------------------------------------------------------------------------------- | 属性值ID | 属性ID | 实体表ID | 实体对象ID | 属性值 | | 1 | 1 | 1 | 1 | Richard| | 2 | 2 | 1 | 1 | Hammer | | 3 | 1 | 1 | 2 | Bruce | | 4 | 2 | 1 | 2 | Heaton | | 5 | 4 | 2 | 1 | BatCave| | 6 | 5 | 2 | 1 |+49123456789 | | 7 | 4 | 2 | 2 |BigCompany | | 8 | 5 | 2 | 2 | 987654321 | | 9 | 6 | 2 | 2 | 147852369 | | 10 | 9 | 2 | 2 | Berlin | | 11 | 10 | 2 | 2 | Germany | | 12 | 11 | 2 | 2 | 14167 | -----------------------------------------------------------------------------------------
日期时间类型属性值表 ----------------------------------------------------------------------------------------- | 属性值ID | 属性ID | 实体表ID | 实体对象ID | 属性值 | | 1 | 3 | 1 | 1 |1985-05-31| -----------------------------------------------------------------------------------------
数字类型属性值表 ----------------------------------------------------------------------------------------- | 属性值ID | 属性ID | 实体表ID | 实体对象ID | 属性值 | | 1 | 7 | 2 | 1 | 1.402636 | | 2 | 8 | 2 | 1 | 7.273922 | -----------------------------------------------------------------------------------------
(属性值文本和属性值布尔表为空)
正如您所见,每个实体的对象并不一定具有相同的属性(属性)。域真的很松散。
因此,像之前许多人一样,我不确定如何以可读的方式检索所有这些信息,即如何获取有关Person表或Site表记录的所有信息?
换句话说,我该如何获得类似于下面这样的东西:
Person表查看 ---------------------------------------------------- | Person ID | 属性代码 | 属性值 | | 1 | PERSON_FIRST_NAME | Richard | | 1 | PERSON_LAST_NAME | Hammer | | 1 | PERSON_BIRTH_DATE | 1985-05-31| | 2 | PERSON_FIRST_NAME | Bruce | | 2 | PERSON_LAST_NAME | Heaton | ----------------------------------------------------
Site表查看 -------------------------------------------------------- | Site ID | 属性代码 | 属性值 | | 1 | SITE_NAME | Batcave | | 1 | SITE_PHONE_NR_1 | +49123456789 | | 1 | SITE_LATITUDE | 1.402636 | | 1 | SITE_LONGITUDE | 7.273922 | | 2 | SITE_NAME | BigCompany | | 2 | SITE_PHONE_NR_1 | 987654321 | | 2 | SITE_PHONE_NR_2 | 147852369 | | 2 | SITE_CITY | Berlin | | 2 | SITE_COUNTRY | Germany | | 2 | SITE_ZIP_CODE | 14167 | --------------------------------------------------------
或者,如果更容易,可以像这样:
Person表查看
------------------------------------------------------------------------ | Person ID | PERSON_FIRST_NAME | PERSON_LAST_NAME | PERSON_BIRTH_DATE | | 1 | Richard | Hammer | 1985-05-31 | | 2 | Bruce | Heaton | | ------------------------------------------------------------------------
Site表查看
---------------------------------------------------------------------------------------------------------------------------------------- | Site ID | SITE_NAME | SITE_PHONE_NR_1 | SITE_PHONE_NR_2 | SITE_LATITUDE | SITE_LONGITUDE | SITE_CITY | SITE_COUNTRY | SITE_ZIP_CODE | | 1 | Batcave | +49123456789 | | 1.402636 | 7.273922 | | | | | 2 | BigCompany | 987654321 | 147852369 | | | Berlin | Germany | 14167 | ----------------------------------------------------------------------------------------------------------------------------------------
我意识到这可能非常令人困惑。请告诉我您还需要哪些信息或某些部分的更好解释,以便我可以更好地帮助您。
我不认为只需要一个SQL查询(每个实体)就够了。我知道很可能需要多于一个查询,而且这些查询很可能需要由PHP之类的语言进行“组装”,以使其真正动态化。所以即使有人能够仅仅解释一下我如何获取上述虚构属性的所有信息,我也会非常感激!
感谢您的任何帮助!
SELECT
查询列的类型不能依赖于表的内容,它必须在查询时知道,或者可以从表列或表达式的类型中发现,因此,编写一个能够产生像第一个示例输出的查询的唯一方法就是连接每个result_value_...
表并合并... - j_random_hackerCOALESCE()
或类似的方法将结果合并到单个字段中。但是,列的类型必然会变成一些最低公共分母类型(可能是无界的VARCHAR
),因此您无法对它们执行大多数明智的操作(例如,即使您知道它们最初是数字,也不能将5添加到每个值中,而不首先显式地转换为数字类型)。一些RDBMS(也许是MySQL,我不知道)具有“枢轴”扩展,可以像您稍后的代码片段那样将行转换为列,但据我所知,这不是标准SQL。 - j_random_hackerALTER TABLE
命令,但我认为更好(更安全)的解决方案是... - j_random_hackerALTER TABLE
相比,我更喜欢这种方法,因为这种方法似乎不太可能意外破坏某些重要的标准列。 无论哪种方式,与您的EAV解决方案相比,DB“知道”数据的真实结构,因此可以对其进行索引,在WHERE
和SELECT
子句中评估表达式等。 - j_random_hacker