在 PostgreSQL 中匹配子查詢 CTE 的多個組件

November 10, 2016

我有一個應用程序以不同的方式授予使用者訪問權限，並且我正在嘗試編寫一個查詢，通過將訪問權限分解為 CTE，以所有不同的方式確定訪問權限，但可讀。
這是我正在談論的基本模式：
WITH user_allowed_data1 AS (
   SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
   SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name FROM user_table
LEFT OUTER JOIN content_table ON 1=1
WHERE u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_1)
OR u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_2)
所以，兩個問題：
有沒有更好/更快/更清潔的方式來考慮這個查詢？
有沒有辦法在這樣的 where 子句中匹配從子查詢中拉出的多個欄位？

有幾種方法可以重寫查詢，即使保留 CTE。但是請考慮 Postgres 中的 CTE 是預先評估的，因此它們可能會導致效率問題。
首先，LEFT連接相當於一個內部，因為您在以下子句JOIN中有一個來自右側表的列。WHERE
二FROM user_table LEFT OUTER JOIN content_table ON 1=1是尷尬。您可以使用簡單的FROM user_table CROSS JOIN content_table. 沒有任何性能提升，只是更簡單。我想我什至更FROM user_table, content_table喜歡LEFT ... ON 1=1.
這樣做之後，很容易看到FROM a CROSS JOIN b WHERE <x>可以替換為FROM a JOIN b ON <x>
IN (SELECT user_id, content_id FROM user_allowed_data_1)可以換成更優雅的IN (TABLE user_allowed_data_1)
現在，要擺脫OR，一種方法是將兩個 ctes 合二為一：
-- query 1 --
WITH user_allowed_data AS (
   SELECT u.id AS user_id, c.id AS content_id FROM …
 UNION DISTINCT
   SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name 
FROM user_table AS u
 JOIN content_table AS c 
   ON (u.id, c.id) IN (TABLE user_allowed_data) ;
但既然我們這樣做了，我們可以只在主查詢中使用連接，而不是花哨的ON ... IN (...)：
-- query 2 --
WITH user_allowed_data AS (
   SELECT u.id AS user_id, c.id AS content_id FROM …
 UNION DISTINCT
   SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name 
FROM user_table AS u
 JOIN user_allowed_data AS ad
   ON ad.user_id = u.id
 JOIN content_table AS c 
   ON ad.content_id = c.id ;
另一種方法 - 擺脫OR- 將UNION在主查詢中使用：
-- query 3 --
WITH user_allowed_data1 AS (
   SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
   SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name 
FROM user_table AS u
 JOIN user_allowed_data1 AS ad
   ON ad.user_id = u.id
 JOIN content_table AS c 
   ON ad.content_id = c.id 
UNION DISTINCT
SELECT u.name, c.name 
FROM user_table AS u
 JOIN user_allowed_data2 AS ad
   ON ad.user_id = u.id
 JOIN content_table AS c 
   ON ad.content_id = c.id ;
當然，上面的任何內容都可以在沒有 ctes 的情況下編寫——我再次強調——可能是效率的障礙。因此，您的原始查詢稍作更改且沒有 CTE（保留OR）：
-- query 1b --
SELECT u.name, c.name 
FROM user_table AS u
 JOIN content_table AS c 
   ON (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …)
   OR (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …) ;
OR變體也可以通過用相關子查詢替換來IN表示EXISTS：
-- query 1c --
SELECT uu.name, cc.name 
FROM user_table AS uu
 JOIN content_table AS cc 
   ON EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
              WHERE (u.id, c.id) = (uu.id, cc.id) )
   OR EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
              WHERE (u.id, c.id) = (uu.id, cc.id) ) ;