sql - PostgreSQL - 复杂的查询设计

Question

我面临设计问题，我有三个表:

var_storage:
id | var_name | var_value | user


post:
id | user

post_var_storage:
post_id | var_storage_id

基本上是 ManyToMany var_storage 的帖子。 var_storage 包含 var 值，我们可以有类似

的数据

var_storage(,MEAL,DINNER,)

var_storage(,食物,薯条,)

等...

当用户创建帖子时，我们会对其所有 var_storage 进行快照，这就是多对多的原因。 (并且不发布 OneToMany var_storage，因为其他实体以相同的方式快照 var_storage)。

然后，我们需要查询数据库以找到所有已创建的具有特定变量值的帖子。

例子:

• “查找使用 MEAL=DINNER 创建的帖子中的所有行”

• “从使用 MEAL=DINNER AND FOOD=FRIES 创建的帖子中查找所有行”

• 等...

过滤是在用户端对多个可能的变量完成的，因此我们无法预测用户想要过滤多少变量。

我设置了一个查询模式以查看它的外观，我想知道是否有更简单的方法来实现它。因为在这个中有与过滤变量一样多的 EXISTS 子句(可以用 15 个变量过滤!!)，并且嵌套的子查询非常庞大，有多个 JOIN 等。

特别是因为其中之一总是相同的(一个是 FROM 子句)。

SELECT * FROM 
post p
WHERE

    # MEAL=DINNER
    EXISTS (
        SELECT * FROM (
            SELECT * 
            FROM post p2 
            INNER JOIN post_var_storage pvs ON pvs.post_id = p2.id 
            INNER JOIN vars_storage vs ON pvs.var_storage_id = vs.id
            WHERE p2.id = p.id
        ) vs 
        WHERE 
            vs.var_name = "MEAL" AND vs.value = "DINNER"
    ) AND

    # FOOD=FRIES
    EXISTS (
        SELECT * FROM (
            SELECT * 
            FROM post p2 
            INNER JOIN post_var_storage pvs ON pvs.post_id = p2.id 
            INNER JOIN vars_storage vs ON pvs.var_storage_id = vs.id
            WHERE p2.id = p.id
        ) vs 
        WHERE 
            vs.var_name = "FOOD" AND vs.value = "FRIES"
    ) AND

    ....

Answer 1

您可以使用 CTE(公用表表达式)来减少 sql 代码，如下所示:

WITH cte AS (
  SELECT p.id pid, var_name vn, var_value vv 
  FROM post p
  INNER JOIN post_var_storage pvs ON pvs.post_id = p.id 
  INNER JOIN vars_storage vs ON pvs.var_storage_id = vs.id
)
select * from  post 
 where exists (select 1 from cte where id=pid and vn='Meal' and vv='Dinner')
   and exists (select 1 from cte where id=pid and vn='Food' and vv='Fries')
// and exists (select 1 from cte where id=pid and vn= ...   and vv= ...   )

看看我在这里准备的 fiddle :http://rextester.com/TPGJ34272

有测试数据

vars_storage:
--------------
id  var_name    var_value
1   Food        Fries
2   Meal        Dinner
3   Breakfast   Cereals
4   Supper      Soup

post:
------
id  user
1   Charles
2   Fiona
3   Patty
4   Joe
5   Rita
6   Harry
7   Meghan
8   Anne

post_var_storage:
------------------
post_id var_storage_id
1       1
1       2
2       4
3       1
3       2
4       2
4       3
5       4
5       1
6       1
6       2

以上查询返回:

id  user
6   Harry
1   Charles
3   Patty