All geek questions in one place

How to request all object information in Entity-Attribute-Value (EAV) model?

I searched about it and tried to do it myself in the last few days, but I just couldn't. The closest I received in my search this answer, also on Stack Overflow: EAV Select a query from tables with extended values

So here I am, turning myself into the Internet!

So, I have a database that uses the EAV (Entity-Attribute-Value) model. But here's the catch: the actual objects are not directly related to other EAV tables. Let me be more specific; let's say that there are tables Person and Site, and that they have only their primary keys: person_idand site_id, respectively.

Since the attributes (called "properties" in my schema) of these objects (i.e., Person and Site) must be dynamic, they must all be stored outside their respective tables, i.e. EAV tables. The following is part of the EAV database schema (I'm not sure if it is fully fixed, so please let me know if you have any suggestions). - http://i.stack.imgur.com/EN3dy.png

Part of the EAV scheme basically has the following tables:

  • property
  • property_value_varchar
  • property_value_text
  • property_value_number
  • property_value_boolean
  • property_value_datetime
  • entity_tables

So, since the objects are not “directly linked” to the EAV part, I use the table entity_tablesas a link to the actual tables, so in the above example, the entity_tablestable should look something like this:

---------------------------------------
| entity_table_id | entity_table_name |
| 1 | person |
|      2         |       site         |
|      .         |        .           |
|      .         |        .           |
---------------------------------------

property - , , , "PERSON_FIRST_NAME" "LOCATION_NAME", - .

property_value_* , property_value. , Entity, entity_table_id entity_object_id.

:

Person table
-------------
| person_id |
|     1     |
|     2     |
-------------

Site table
-----------
| site_id |
|    1    |
|    2    |
-----------

entity_tables table
---------------------------------------
|entity_table_id | entity_table_name  |
|      1         |       person       |
|      2         |       site         |
---------------------------------------

property table
-------------------------------------
| 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 table
-----------------------------------------------------------------------------------------
| property_value_id | property_id | entity_table_id | entity_object_id | property_value |
|         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     |
-----------------------------------------------------------------------------------------

property_value_datetime table
-----------------------------------------------------------------------------------------
| property_value_id | property_id | entity_table_id | entity_object_id | property_value |
|         1         |      3      |        1        |         1        |   1985-05-31   |
-----------------------------------------------------------------------------------------

property_value_number table
-----------------------------------------------------------------------------------------
| 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 and property_value_boolean tables are empty)

, (). .

, , , , , , , Person ?

, - :

 Person table view
----------------------------------------------------
| Person ID |     Property code   | Property value |
|     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 table view
------------------------------------------------
| Site ID | Property code    |  Property value |
|    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 table view
------------------------------------------------------------------------
| Person ID | PERSON_FIRST_NAME | PERSON_LAST_NAME | PERSON_BIRTH_DATE |
|     1     |      Richard      |       Hammer     |     1985-05-31    |
|     2     |       Bruce       |       Heaton     |                   |
------------------------------------------------------------------------

Site table view
----------------------------------------------------------------------------------------------------------------------------------------
| 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     |
----------------------------------------------------------------------------------------------------------------------------------------

, . , , , - .

, 1 SQL ( ) . , 1 /, , "" PHP (), . , - , (), , !

!

+4
1

! sql. . , entity_table_id , entity_object_id __ + '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)
+1

Source: https://habr.com/ru/post/1527899/

More articles:


All Articles