sql - Oracle 实体属性值 (EAV) 数据库表中带有 WHERE 子句的 LEFT JOIN 列

Question

我有一个像这样的 USERS 表

用户

user_id | username
1         tom
2         sam

我也有一个像这样的 USER_META 表

USER_META

user_meta_id | user_id | meta_key | meta_value
1              1         active     1
2              1         car        dodge
3              2         active     0
4              2         car        honda

我的问题是，我需要选择 meta_keys active 和 car，但仅适用于 active 值为 1 的用户，因此我的结果集应该是这样的

user_id | user_name | user_meta_id | meta_key | meta_value
1         tom         1              active     1
1         tom         2              car        dodge

我已经尝试了一些东西，但我无法得到这种类型的结果集。这是我认为可能有效的方法

SELECT * FROM USERS
LEFT JOIN USER_META 
   ON USERS."user_id" = USER_META 
   AND (USER_META."meta_key" = 'active' OR USER_META."meta_key" = 'car') 
WHERE (USER_META."meta_key" = 'active' 
AND USER_META."meta_value" = 1)

问题是我得到的结果集缺少汽车元键/元值

user_id | user_name | user_meta_id | meta_key | meta_value
1         tom         1              active     1

如何修改查询以获取所有元信息？谢谢。

Answer 1

首先，这种实体-属性-值数据模型通常是一个非常糟糕的想法。您基本上是在重新实现关系数据库为您提供的开箱即用的功能，并且查询很快就会变得非常笨拙。通常，您需要加入 USER_META表 N 次，以便将 N 个属性作为列或为了在您的数据上有 N 个谓词。

在数据建模方面，如果您避免创建区分大小写的列名，以便他们不必每次都用双引号引用每个标识符，那么 future 的开发人员通常会很感激。

由于您需要两个键，因此您应该能够执行以下操作(我假设 meta_value 始终存储为字符串，即使它表示数字或 bool 值)

SELECT usr."user_id",
       usr."user_name",
       meta."user_meta_id",
       meta."meta_key",
       meta."meta_value"
  FROM users usr
       JOIN user_meta meta ON (usr."user_id" = meta."user_id")
 WHERE meta."meta_key" in ('active', 'car')
   AND usr."user_id" IN (SELECT active."user_id"
                           FROM user_meta active
                          WHERE active."meta_key" = 'active'
                            AND active."meta_value" = '1' )

它返回您发布的数据的预期结果

SQL> ed
Wrote file afiedt.buf

  1  with users as (
  2    select 1 "user_id", 'tom' "user_name" from dual union all
  3    select 2, 'sam' from dual
  4  ),
  5  user_meta as (
  6    select 1 "user_meta_id",
  7           1 "user_id",
  8           'active' "meta_key",
  9           '1' "meta_value" from dual union all
 10    select 2, 1, 'car', 'dodge' from dual union all
 11    select 3, 2, 'active', '0' from dual  union all
 12    select 4, 2, 'car', 'honda' from dual
 13  )
 14  SELECT usr."user_id",
 15         usr."user_name",
 16         meta."user_meta_id",
 17         meta."meta_key",
 18         meta."meta_value"
 19    FROM users usr
 20         JOIN user_meta meta ON (usr."user_id" = meta."user_id")
 21   WHERE meta."meta_key" in ('active', 'car')
 22     AND usr."user_id" IN (SELECT active."user_id"
 23                             FROM user_meta active
 24                            WHERE active."meta_key" = 'active'
 25*                             AND active."meta_value" = '1' )
SQL> /

   user_id use user_meta_id meta_k meta_
---------- --- ------------ ------ -----
         1 tom            1 active 1
         1 tom            2 car    dodge