如何在实体-属性-值 (EAV) 模型中查询实体的所有信息？

Question

如何在实体-属性-值 (EAV) 模型中查询实体的所有信息？

4

我已经搜索过这个问题并在过去的几天里试图自己解决，但我一直无法做到。在我的搜索中，最接近的答案是Stack Overflow上的这个回答：EAV Select query from spreaded value tables。

因此，我转向了互联网寻求帮助！

我有一个使用 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之类的语言进行“组装”，以使其真正动态化。所以即使有人能够仅仅解释一下我如何获取上述虚构属性的所有信息，我也会非常感激！

感谢您的任何帮助！

- Ashyam90

1

不幸的是，EAV通常是一种反模式。它使得你想要的查询变得非常笨重，你最终不得不“硬编码”很多你已经小心避免“硬编码”到模式中的东西，失去了任何好处。特别是，一个SELECT查询列的类型不能依赖于表的内容，它必须在查询时知道，或者可以从表列或表达式的类型中发现，因此，编写一个能够产生像第一个示例输出的查询的唯一方法就是连接每个result_value_...表并合并... - j_random_hacker

使用COALESCE()或类似的方法将结果合并到单个字段中。但是，列的类型必然会变成一些最低公共分母类型（可能是无界的VARCHAR），因此您无法对它们执行大多数明智的操作（例如，即使您知道它们最初是数字，也不能将5添加到每个值中，而不首先显式地转换为数字类型）。一些RDBMS（也许是MySQL，我不知道）具有“枢轴”扩展，可以像您稍后的代码片段那样将行转换为列，但据我所知，这不是标准SQL。 - j_random_hacker

2

另一方面，如果您选择使用EAV允许用户在表中添加列的最大灵活性，则必须接受这种灵活性意味着DB“几乎不知道”该数据，因此除了将其视为任意文本或二进制数据块之外，将无法对其进行更多处理。（例如，正如您会发现的那样，尝试将此类“列”允许到几种不同类型是一场噩梦，并且忘记尝试按此列进行索引。）最后，EAV基本上就是这个：http://en.wikipedia.org/wiki/Inner-platform_effect - j_random_hacker

1

如果您想要为这些用户提供完全的灵活性，那么您实际上是在编写一个通用的RDBMS前端——我相信这样的东西已经存在了。但我认为您可能只想提供给他们一些灵活性。在这种情况下，我建议在您的数据库模式中设置固定的表格（和索引等）用于主要的不会更改的业务实体。如果您想要为用户提供向这些表格的行添加额外属性的能力，您可以从他们的输入创建ALTER TABLE命令，但我认为更好（更安全）的解决方案是... - j_random_hacker

只允许他们在模式中创建或删除完全新的DB表，这些表的主键（部分或全部）是您的“标准”表之一的外键。（通过创建具有相同PK的另一个表，可以有效地“添加列”到现有表中。）与ALTER TABLE相比，我更喜欢这种方法，因为这种方法似乎不太可能意外破坏某些重要的标准列。无论哪种方式，与您的EAV解决方案相比，DB“知道”数据的真实结构，因此可以对其进行索引，在WHERE和SELECT子句中评估表达式等。 - j_random_hacker

显示剩余3条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ron Smith · Accepted Answer

这是一个有趣的问题！可以使用动态SQL来处理。在下面的代码中，模式已经用临时表重新创建。该代码可以转换为存储过程，以entity_table_id作为参数，然后选择entity_object_id作为entity_table_name+'id'，接着选择每个property_value作为列，并将相应的property_code作为标题。

-- load EAV tables
if object_id('tempdb..#entity_tables') is not null
    drop table #entity_tables
create table #entity_tables(entity_table_id int,entity_table_name varchar(255))
insert into #entity_tables values
    (1,'person'),
    (2,'site')
if object_id('tempdb..#property') is not null
    drop table #property
create table #property(property_id int,property_code varchar(255))
insert into #property values
    (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')
if object_id('tempdb..#property_value_varchar') is not null
    drop table #property_value_varchar
create table #property_value_varchar(property_value_id int,property_id int,entity_table_id int,entity_object_id int,property_value varchar(255))
insert into #property_value_varchar values
    (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')
if object_id('tempdb..#property_value_datetime') is not null
    drop table #property_value_datetime
create table #property_value_datetime(property_value_id int,property_id int,entity_table_id int,entity_object_id int,property_value datetime)
insert into #property_value_datetime values
(1,3,1,1,'1985-05-31')
if object_id('tempdb..#property_value_number') is not null
    drop table #property_value_number
create table #property_value_number(property_value_id int,property_id int,entity_table_id int,entity_object_id int,property_value float)
insert into #property_value_number values
(1,7,2,1,1.402636),
(2,8,2,1,7.273922)

-- create dynamic sql to get all data conditioned on #entity_tables.table_id value
declare @tableid int,@sql varchar(max)
set @tableid = 1 -- this could be passed as a parameter

-- get pivot code with #ColumnList# placeholders to be added below
select @sql = 'select entity_object_id ' + entity_table_name + 'id,
    #ColumnListCast#
from    (
        select
            e.entity_table_name,
            pv.entity_object_id,
            pv.property_value,
            p.property_code
        from #entity_tables e
            inner join  (
                        select entity_table_id,entity_object_id,property_id,property_value from #property_value_varchar union all
                        select entity_table_id,entity_object_id,property_id,cast(property_value as varchar(255)) from #property_value_datetime union all
                        select entity_table_id,entity_object_id,property_id,cast(property_value as varchar(255)) from #property_value_number
                        ) pv
                on pv.entity_table_id = e.entity_table_id
            inner join #property p
                on p.property_id = pv.property_id
        where e.entity_table_id = ' + cast(@tableid as varchar(5)) + '
        ) p
    pivot   (
            max(property_value)
            for property_code in    (
                                    #ColumnList#
                                    )
            ) piv' from #entity_tables where entity_table_id = @tableid

-- get column list with cast version for diffferent data types
declare @ColumnList varchar(max),
        @ColumnListCast nvarchar(max)
set @ColumnList = ''
set @ColumnListCast = ''
select  @ColumnList = @ColumnList + '[' + p.property_code + ']' + case row_number() over(order by p.property_id desc) when 1 then '' else ',' end,
        @ColumnListCast = @ColumnListCast + 'cast([' + p.property_code + '] as ' + t.CastValue + ') [' + p.property_code + ']' + case row_number() over(order by p.property_id desc) when 1 then '' else ',' end
from #property p
    inner join  (
                select property_id,'varchar(255)' CastValue from #property_value_varchar where entity_table_id = @tableid union
                select property_id,'datetime' CastValue from #property_value_datetime where entity_table_id = @tableid union
                select property_id,'float' CastValue from #property_value_number where entity_table_id = @tableid
                ) t
        on t.property_id = p.property_id
order by p.property_id

set @sql = replace(replace(@sql,'#ColumnList#',@ColumnList),'#ColumnListCast#',@ColumnListCast)

exec(@sql)