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

Question

我已经搜索过此内容，并尝试在过去几天内自行制作，但我做不到。我在搜索中得到的最接近的答案是这个答案，也在Stack Overflow上：EAV Select query from spreaded value tables

所以我在这里，将自己转向互联网！

因此，我有一个利用EAV（实体-属性-值）模型的数据库。但是这里有个要点：实际实体没有直接连接到其他EAV表。让我更具体一些；说有个人和站点表，并且它们只有主键：分别为person_id和site_id。

由于这些实体（即“人”和“站点”）的属性（在我的架构上称为“属性”）必须是动态的，因此它们必须全部存储在各自的表（即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 |实体表名称|
| 1 |人
| 2 |网站|
| 。 | 。 |
| 。 | 。 |
---------------------------------------


property表是实际上具有任何实体可以拥有的不同属性的表，例如“ PERSON_FIRST_NAME”或“ LOCATION_NAME”，或其他。

除了property_value_*的数据类型外，property_value表都完全相同。这些是保存每个实体对象属性的实际值的对象，它们由entity_table_id和entity_object_id映射。

为了清楚起见，让我给您一个可能的数据库实例：

人表
-------------
| person_id |
| 1 |
| 2 |
-------------

站点表
-----------
| site_id |
| 1 |
| 2 |
-----------

实体表表
---------------------------------------
| entity_table_id |实体表名称|
| 1 |人
| 2 |网站|
---------------------------------------

属性表
-------------------------------------
| property_id | property_code |
| 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 |
-------------------------------------

property_value_varchar表
-------------------------------------------------- ---------------------------------------
| property_value_id | property_id | entity_table_id | entity_object_id | property_value |
| 1 | 1 | 1 | 1 |理查德|
| 2 | 2 | 1 | 1 |锤子|
| 3 | 1 | 1 | 2 |布鲁斯|
| 4 | 2 | 1 | 2 |希顿|
| 5 | 4 | 2 | 1 |蝙蝠洞|
| 6 | 5 | 2 | 1 | +49123456789 |
| 7 | 4 | 2 | 2 |大公司|
| 8 | 5 | 2 | 2 | 987654321 |
| 9 | 6 | 2 | 2 | 147852369 |
| 10 | 9 | 2 | 2 |柏林|
| 11 | 10 | 2 | 2 |德国|
| 12 | 11 | 2 | 2 | 14167 |
-------------------------------------------------- ---------------------------------------

property_value_date时间表
-------------------------------------------------- ---------------------------------------
| property_value_id | property_id | entity_table_id | entity_object_id | property_value |
| 1 | 3 | 1 | 1 | 1985-05-31 |
-------------------------------------------------- ---------------------------------------

property_value_number表
-------------------------------------------------- ---------------------------------------
| property_value_id | property_id | entity_table_id | entity_object_id | property_value |
| 1 | 7 | 2 | 1 | 1.402636 |
| 2 | 8 | 2 | 1 | 7.273922 |
-------------------------------------------------- ---------------------------------------

（property_value_text和property_value_boolean表为空）




如您所见，并非每个实体的所有对象都必须具有相同的属性（属性）。这样的领域真的很松散。

因此，现在像我之前的许多人一样，我不确定如何以可读的方式检索所有这些信息，即如何获得与Person表或Site表的记录有关的所有信息？

即，我如何获得这样的东西：

人表视图
-------------------------------------------------- -
|人名|物业代码|物业价值|
| 1 | PERSON_FIRST_NAME |理查德|
| 1 | PERSON_LAST_NAME |锤子|
| 1 | PERSON_BIRTH_DATE | 1985-05-31 |
| 2 | PERSON_FIRST_NAME |布鲁斯|
| 2 | PERSON_LAST_NAME |希顿|
-------------------------------------------------- -

站点表视图
------------------------------------------------
|网站编号|物业代码|物业价值|
| 1 | SITE_NAME |蝙蝠洞|
| 1 | SITE_PHONE_NR_1 | +49123456789 |
| 1 | SITE_LATITUDE | 1.402636 |
| 1 | SITE_LONGITUDE | 7.273922 |
| 2 | SITE_NAME |大公司|
| 2 | SITE_PHONE_NR_1 | 987654321 |
| 2 | SITE_PHONE_NR_2 | 147852369 |
| 2 | SITE_CITY |柏林|
| 2 | SITE_COUNTRY |德国|
| 2 | SITE_ZIP_CODE | 14167 |
------------------------------------------------


甚至像这样，如果更容易：

人表视图
-------------------------------------------------- ----------------------
|人名| PERSON_FIRST_NAME | PERSON_LAST_NAME | PERSON_BIRTH_DATE |
| 1 |理查德|锤子| 1985-05-31 |
| 2 |布鲁斯|希顿| |
-------------------------------------------------- ----------------------

站点表视图
-------------------------------------------------- -------------------------------------------------- ------------------------------------
|网站编号| SITE_NAME | SITE_PHONE_NR_1 | SITE_PHONE_NR_2 | SITE_LATITUDE | SITE_LONGITUDE | SITE_CITY | SITE_COUNTRY | SITE_ZIP_CODE |
| 1 |蝙蝠洞| +49123456789 | | 1.402636 | 7.273922 | | | |
| 2 |大公司| 987654321 | 147852369 | | |柏林|德国| 14167 |
-------------------------------------------------- -------------------------------------------------- ------------------------------------


我意识到这可能会令人困惑。请让我知道我还能如何帮助您，例如获得更多信息或对某些部分进行更好的解释。

我也不希望有1个SQL查询（每个实体）能达到目的。我意识到，可能要进行1个以上的查询，并且很有可能需要通过PHP（例如）将其“组装”，以使其真正具有动态性。因此，即使有人甚至可以向我解释我如何仅出于上面的假设属性（属性）获得所有这些信息，我也将非常感激！

感谢您的任何帮助！

Answer 1

这是一个有趣的问题！可以使用动态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)