CODEWITHSUNDEEP
mysqlsqlentity-attribute-value
Ashyam90
Ashyam90

Reputation: 71

How to query for all information of an entity within an Entity-Attribute-Value (EAV) model?

I've searched about this and tried to make it myself over the last few days but I just couldn't. The closest I've gotten within my search was this answer, also on Stack Overflow: EAV Select query from spreaded value tables

So here I am, turning myself to the Internet!

So, I have a database which makes use of the EAV (Entity-Attribute-Value) model. But here's the catch: the actual entities aren't directly connected to the other EAV tables. Let me be more specific; say that there is a Person and a Site tables, and that they only have their primary keys: person_id and site_id, respectively.

Because the attributes (called "properties" on my schema) of those entities (i.e. the Person and the Site) must be dynamic, they must all be stored outside their respective tables, i.e. the EAV tables. The following is the EAV part of the database schema (I'm not sure if it's completely correct, so please let me know if you have any suggestion). -- https://i.sstatic.net/EN3dy.png

The EAV part of the schema basically has the following tables:

Ok, so, since the Entities aren't "directly connected" to the EAV part, I'm using the entity_tables table just as a reference to the actual tables, so, with the example above, the entity_tables table should look something like this:

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

The property table is the one that actually holds the different properties that any entity can hold, say "PERSON_FIRST_NAME" or "LOCATION_NAME", or anything else.

The property_value_* tables are all exactly the same except on the datatype of the property_value. These are the ones that hold the actual value of each Entity's object's property, which get mapped by the entity_table_id and entity_object_id.

Let me give you a possible instance of the database, for clarity:

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)

As you could see, not all objects of each entity have necessarily the same properties (attributes). The domain is really loose like that.

So, now like so many people before me, I'm not sure how to retrieve all of this information in a readable way, namely how can I get all of the information concerning the records of the Person table or of the Site table?

Namely, how can I get something like this:

 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          |
------------------------------------------------

Or even like this, if it is easier:

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     |
----------------------------------------------------------------------------------------------------------------------------------------

I realize this can be quite confusing. Please let me know how else I can help you help me, like more information or better explaining of some part.

I also don't expect 1 SQL query (per entity) to do the trick. I realize that more than 1 query is likely and that it/they would very likely need to be "assembled" by PHP (for instance), in order to really make it dynamic. So even if someone could even just explain me how I could get all of this info just for the hypothetical properties (attribute) that I have above, I would already be immensely grateful!

Thank you for any help!

Upvotes: 4

Views: 2953

Answers (1)

Ron Smith
Ron Smith

Reputation: 3266

This was a fun question! This can be handled with dynamic sql. In the code below, the schema has been recreated with temp tables. The code could be turned into a stored procedure that takes an entity_table_id as a parameter and then selects the entity_object_id as entity_table_name + 'id' followed by every property_value as columns with the corresponding property_code as the headings.

-- 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)

Upvotes: 1

Related Questions