查询EAV SQL设计 [英] Querying on EAV SQL Design
问题描述
我有3张这样的桌子.
Entity_Table
|e_id|e_name|e_type |e_tenant|
|1 | Bob | bird | owner_1|
|2 | Joe | cat | owner_1|
|3 | Joe | cat | owner_2|
AttributeValue_Table
|av_id|prop_name |prop_value|
|1 | color | black |
|2 | color | white |
|3 | wing size| 7" |
|4 | whiskers | long |
|5 | whiskers | short |
|6 | random | anything |
Entity_AttrVal
|e_id|av_id|
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
| 2 | 5 |
| 3 | 1 |
| 3 | 4 |
| 3 | 6 |
我想做的是类似查找实体,其中e_name ='Joe'和color = black and whiskers = short.
What I want to be able to do is something like 'find entity where e_name='Joe' and color=black and whiskers=short.
我可以获得一个结果集,其中每一行具有1个prop/值以及实体信息,因此对一个属性进行查询是可行的.但是我需要能够执行任意N个属性.我该怎么做?
I can obtain a result set where each row has 1 prop/value, along with the entity information, so querying on one property works. But I need to be able to do arbitrary N properties. How do I do something like this?
我可以使用所有属性作为列或其他内容来构建联接表
Can I build a join table with all properties as columns or something
edit2:看起来我可以做这样的事
edit2: Looks like I can do something like this
SELECT et.e_id, et.e_name, et.e_type
FROM Entitiy_table et
LEFT JOIN Entity_AttrVal j ON et.e_id = j.e_id
RIGHT JOIN AttributeValue_Table at ON at.av_id = j.av_id
WHERE (av.prop_name='color' AND av.prop_value='white') OR (av.prop_name='whiskers' AND av.prop_value='long')
GROUP BY et.e_id, et.e_name, et.e_type
HAVING COUNT(*) = 2;
推荐答案
您必须为每个名称/值组合添加一个谓词:
You have to add a predicate for each name/value combination:
SELECT <whatever you need>
FROM Entity_Table et
WHERE et.e_name = 'Joe'
AND EXISTS (SELECT 1
FROM AttributeValue_Table avt
JOIN Entity_AttrVal ea ON ea.e_id = et.e_id
WHERE ea.a_id = avt.av_id
AND avt.prop_name = 'color'
AND avt.prop_value = 'black')
AND EXISTS (SELECT 1
FROM AttributeValue_Table avt
JOIN Entity_AttrVal ea ON ea.e_id = et.e_id
WHERE ea.a_id = avt.av_id
AND avt.prop_name = 'whiskers'
AND avt.prop_value = 'short')
(如果我的Sql Server方言闪闪发光,我深表歉意)
(I apologize if my Sql Server dialect shines through)
要进行任意数量的比较,您必须生成SQL并执行它.
To do an arbitrary number of comparisons, you'd have to generate the SQL and execute it.
正如评论中所说,这表明EAV是一种痛苦(实际上是一种反模式),但我从经验中知道,有时如果我们绑定到关系数据库,别无选择.
As said in a comment, this goes to show that EAV is a pain (an anti-pattern, really), but I know by experience that sometimes there's simply no alternative if we're bound to a relational database.
这篇关于查询EAV SQL设计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!