CODEWITHSUNDEEP
sql-serverwhere-clauseentity-attribute-value
ang
ang

Reputation: 1581

How to multi-select filter an EAV table in SQL Server

I have an EAV table with attributes and would like to do a hybrid selection of the items based on variables that are passed into a stored procedure.

Sample table:

| group_id | item_id | key    | value |
+----------+---------+--------+-------+
| 1        | AA      | length | 10    |
| 1        | AA      | width  | 10    | 
| 1        | AA      | color  | white |
| 1        | AA      | brand  | beta  |
| 1        | BB      | length | 25    |
| 1        | BB      | brand  | alpha |
| 2        | CC      | brand  | alpha |

Sample query:

declare @attributes nvarchar(max) = 'brand name, length'
declare @attributeValues nvarchar(max) = 'alpha, beta, 25'
declare @id int = 1

select *
into #allProductsFromGroup
from items
where group_id = @id

select item_id
from #allProductsFromGroup #all
where [key] in (select value from string_split(@attributes, ',')) 
  and [value] in (select value from string_split(@attributeValues, ','))

Expected output:

| item_id |
+---------+
| BB      |

I could hard-code in and and or statements for each key, but there are many, and I am looking for a more scalable solution.

Passing in and parsing JSON would be good, like:

[
  { "brand": "aplha" },
  { "brand": "beta" },
  { "length": 25 }
]

How can I write the second select to dynamically return a subset of allProductsFromGroup that dynamically include multiple results from the same group (multi-select brand or multi-select length), but exclude from other groups (color, length, etc.)?

Upvotes: 1

Views: 215

Answers (2)

Zhorov
Zhorov

Reputation: 29973

It's probably a late answer, but if you can pass conditions as JSON, the next approach is also a possible solution. The JSON must be in the same format as in the answer and you may use more than two conditions:

Table:

CREATE TABLE Data (
   group_id int,
   item_id varchar(2),
   [key] varchar(100),
   [value] varchar(100)
)
INSERT INTO Data (group_id, item_id, [key], [value])
VALUES
   (1, 'AA', 'length', '10'),
   (1, 'AA', 'width',  '10'),
   (1, 'AA', 'color',  'white'),
   (1, 'AA', 'brand',  'beta'),
   (1, 'BB', 'length', '25'),
   (1, 'BB', 'brand',  'alpha'),
   (2, 'CC', 'brand',  'alpha')

Conditions as JSON:

DECLARE @conditions varchar(max) = N'
[
   {"key": "brand", "values": ["alpha", "beta"]},
   {"key": "length", "values": ["25"]}
]
'

Statement:

SELECT d.item_id
FROM Data d
JOIN (
   SELECT j1.[key], j2.[value]
   FROM OPENJSON(@conditions) WITH (
      [key] varchar(100) '$.key',
      [values] nvarchar(max) '$.values' AS JSON
   ) j1
   CROSS APPLY OPENJSON(j1.[values]) j2
) o ON d.[key] = o.[key] AND d.[value] = o.[value]
GROUP BY d.item_id
HAVING COUNT(*) = (SELECT COUNT(*) FROM OPENJSON(@conditions))

Result:

item_id
BB

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

The target query might look something like this:

with q as
(
    select item_id,
       max( case when [key] = 'brand' then [value] end ) brand,
       max( case when [key] = 'length' then cast( [value] as int ) end ) length,
    from #allProductsFromGroup 
    group by Item_id
)
select item_id
from q
where brand in ('alpha','beta') and length=25

You just have to build it from the incoming data (yuck). A simpler query form to generate might be something like

select item_id
from #allProductsFromGroup 
where [key] = 'brand' and [value] in ('alpha','beta')
intersect 
select item_id
from #allProductsFromGroup 
where [key] = 'length' and [value] = 25

mapping and criteria to intersect, and or criteria to union. It's likely to be cheaper too, as each query can seek an index on (key,value).

Upvotes: 1

Related Questions