How to retrieve multiple items with all their attributes from an EAV data model?

Question

How could someone retrieve multiple products with all their attributes whose ids are passed as a comma separated list to a stored procedure?

Tables are designed in a simple EAV fashion like so:

tbl_products
  id
  title

tbl_product_attributes
  product_FK
  attribute_FK
  attribute_value

tbl_attributes
  id
  title

The following stored procedure do this for just one product:

CREATE PROCEDURE get_product
  @product_id NVARCHAR(MAX)
AS
  BEGIN
    -- Create a temp table to store values of get_product_attributes sp
    CREATE TABLE #temp ( attributes NVARCHAR(MAX) );

    -- Insert results of get_product_attributes sp into temp table
    INSERT INTO #temp (attributes)
    EXEC get_product_attributes @product_id;

    -- Select product with all its attributes
    SELECT id,
           title,
           ( SELECT attributes FROM #temp ) AS attributes
    FROM   tbl_products
    WHERE  id = @product_id;
  END;

But what if we want multiple product details given the product ids?

(get_product_attributes stored procedure return all attributes and their values of a specific product as json it uses dynamic sql to retrieve all attributes then return them as json; exactly like what dynamic sql part of this answer does: https://stackoverflow.com/a/13092910/5048383)

(also using sql server 2016, got access to STRING_SPLIT function and could easily transform passed ids to rows)

Your last sentence is your solution...you'll join to the function results. Look at the bottom of [this answer](https://stackoverflow.com/a/878964/6167855) (and others) — S3S, Jul 21 '17 at 16:41
@scsimon, of course i could write where clause like `WHERE id IN ( SELECT value from STRING_SPLIT(@product_id, ','))`; but what about attributes part. I think I should loop over get_product_attributes but as i use `guid` for ids I'm having trouble using while loops and I think my best bet could be using cursors and loop over get_product procedure passing each id. any other taught?! — dNitro, Jul 21 '17 at 16:55

S3S · Accepted Answer · 2017-07-21T21:01:27.597

Generally speaking, looping is bad in SQL. For what you described, I can't imagine why it would warrant a loop. However, here is a set based method with simple joins that would eliminate everything but a single procedure.

declare @tbl_products table (
                            id int, 
                            title varchar(16))

insert into @tbl_products
values
(1,'prod1'),
(2,'prod2'),
(3,'prod3'),
(4,'prod4'),
(5,'prod5')

declare @tbl_attributes table (
                                id int,
                                title varchar(16))

insert into @tbl_attributes
values
(1,'attr1'),
(2,'attr2'),
(3,'attr3'),
(4,'attr4'),
(5,'attr5')


declare @tbl_product_attributes table (
                                        product_FK int,
                                        attribute_FK int,
                                        attribute_value varchar(64))

insert into @tbl_product_attributes
values
(1,5,'blah'),
(1,3,'blah blah'),
(2,1,'not sure'),
(2,3,'what should be here'),
(2,4,'but here is a'),
(3,5,'line of text'),
(3,4,'as a place holder')




declare @product_id nvarchar(4000)
set @product_id = '1,3'



if object_id ('tempdb..#staging') is not null
drop table #staging

select
    prod.id
    ,prod.title as ProductTitle
    ,'Attr' + cast(attr.id as char(8)) as AttributeID
    ,attr.title as AttributeTitle
    ,prodAttributes.attribute_value as EAV_AttributeValue
into #staging
from
    @tbl_products prod
inner join
    @tbl_product_attributes prodAttributes
    on prodAttributes.product_FK = prod.id
inner join
    @tbl_attributes attr on
    attr.id = prodAttributes.attribute_FK
inner join
    string_split(@product_id,',') x
    on x.value = prod.id




DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(AttributeID)
FROM (SELECT DISTINCT AttributeID FROM #staging) AS AttributeID


--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'
    SELECT ProductTitle,  ' + @ColumnName + '
    FROM #staging
    PIVOT(min(AttributeTitle) 
          FOR AttributeID IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

So, you would just create the proc on the top part...

create proc yourProc(@product_id nvarchar(4000))
as

select
    prod.id
    ,prod.title as ProductTitle
    ,attr.title as AttributeTitle
    ,prodAttributes.attribute_value as EAV_AttributeValue
from
    @tbl_products prod
inner join
    @tbl_product_attributes prodAttributes
    on prodAttributes.product_FK = prod.id
inner join
    @tbl_attributes attr on
    attr.id = prodAttributes.attribute_FK
where
    prod.id in (select * from string_split(@product_id,','))

NOTE: This can also be written more cleanly as a join

select
    prod.id
    ,prod.title as ProductTitle
    ,attr.title as AttributeTitle
    ,prodAttributes.attribute_value as EAV_AttributeValue
from
    @tbl_products prod
inner join
    @tbl_product_attributes prodAttributes
    on prodAttributes.product_FK = prod.id
inner join
    @tbl_attributes attr on
    attr.id = prodAttributes.attribute_FK
inner join
    string_split(@product_id,',') x
    on x.value = prod.id

Well, but application layer needs a much cleaner output; `id, title, attr1, attr2, ...` and their values into rows. so I need to pivot attribute/value rows to columns and due to attribute dynamicity of every product i could just use dynamic sql in a stored procedure ( and not a function). Using stored procedure values is not that simple of functions in joins or cross applys or other situation. so i first trap them in a table and use it. but every time it returns same attributes and thats obvious because every time it just insert attributes of fist product in temporary table. — dNitro, Jul 21 '17 at 20:28
Seems retrieve all product attributes in aforementioned format is somehow complex by this design. — dNitro, Jul 21 '17 at 20:30
If you need to pivot it out, there is a pivot function for that. I still would not use dynamic sql or loops... and i don't know what you mean by "Using stored procedure values is not that simple of functions in joins or cross applys or other situation. " but i'm almost certain you are over complicating this problem. You have to think "SET BASED" when dealing with SQL. How one may attack a problem in Java or Py differs greatly here. Your original code looks like you are thinking like that.. creating classes and functions to do small blocks of work. That's a bad idea. — S3S, Jul 21 '17 at 20:31
So would you update your answer with pivot part and show us how to retrieve every attribute of passed products into columns?! — dNitro, Jul 21 '17 at 20:36
added pivot to the top part @dNitro... anything else should be asked in a new question — S3S, Jul 21 '17 at 21:01
Tanx man, although at last we've yet used dynamic sql but you show me the right direction. Just after a little search I'm now totally convinced with SET BASED approach and will trying think this way when writing SQL. — dNitro, Jul 21 '17 at 21:16

How to retrieve multiple items with all their attributes from an EAV data model?

1 Answers1