任意查詢 n:m 關係，包括“all”和“any”

我正在使用 postgres >= 9.6。我有任務、標籤和 task_tags 表，用於任務和標籤之間的典型 n:m 關係。

我希望能夠以程式方式針對任務表建構查詢，該表支持針對任務的實際欄位的查詢，也支持任務的標籤（標籤名稱）。

對任務欄位本身的查詢是直截了當的。在標籤上查詢“它是否有標籤 A？” 也是直截了當的。我正在苦苦掙扎的是想出一個選擇/查詢結構，它可以讓我也匹配諸如“它是否有標籤 A 和標籤 B”之類的東西？

我想出的最好的方法是使用數組聚合對子查詢進行橫向連接，然後使用數組匹配函式，例如：

SELECT DISTINCT ON (tasks.id) tasks.* 
FROM   tasks, LATERAL (SELECT array_agg(tags.name) AS tags 
                      FROM   task_tags 
                      INNER JOIN tags 
                      ON task_tags.tag_id = tags.id 
                      WHERE task_tags.task_id = tasks.id 
                      GROUP BY task_tags.task_id) tt
WHERE  tt.tags @> array['tag1'::varchar, 'tag3'::varchar];

這樣，應該可以以程式方式建構 WHERE 子句（使用 tasks.* 和 tt.tags）滿足使用者提供的“查詢”的所有條件。

但是，我不確定這是否是最好的方法 - 想法？這個查詢有效率嗎？我可以創建任何可以改進它的索引嗎？

同樣，有沒有辦法讓它與萬用字元一起使用標籤名稱？正常的數組匹配不允許這樣做，我見過的解決方案建議使用 unnest （或者，好吧，首先不使用數組），但是我會失去說“它需要同時具有 tagA 和標籤B”。

有沒有其他方法可以對這些關係進行查詢，從而允許這種“tagA 和 tagB”匹配？

這個查詢有效率嗎？

不，由於多種原因，它的效率極低。

0. 測試時間表

---

基於這個簡化的模式我的回答：

CREATE TABLE tag (
 tag_id serial PRIMARY KEY
, tag    text NOT NULL
);

CREATE TABLE task (
 task_id  serial PRIMARY KEY
, task     text NOT NULL
);

CREATE TABLE task_tag (
 task_id    int
, tag_id     int
, PRIMARY KEY (tag_id, task_id)  -- columns in this order
, FOREIGN KEY (task_id) REFERENCES task
, FOREIGN KEY (tag_id)  REFERENCES tag;
);

1.查詢效率很低

您的原始查詢，適用於測試模式：

SELECT DISTINCT ON (task.task_id) task.*  -- DISTINCT is dead freight
FROM   task, LATERAL (
  SELECT array_agg(tag.tag) AS tags  -- array_agg more expensive than array constructor
  FROM   task_tag 
  JOIN   tag ON task_tag.tag_id = tag.tag_id 
  WHERE  task_tag.task_id = task.task_id 
  GROUP  BY task_tag.task_id  -- redundant noise
  ) tt
WHERE  tt.tags @> array['tag1'::text, 'tag3'::text];  -- or array literal.

怎麼了？對於初學者：

• 沒有理由添加DISTINCT ON。橫向連接僅在聚合後連接到單行。

• 對於這種簡單的數組聚合，數組建構子更便宜。

  • 為什麼 array_agg() 比非聚合 ARRAY() 建構子慢？

• 無需添加GROUP BY，WHERE子句後已經過濾了一個 task_id。

• 根據您呼叫查詢的方式，傳遞單個數組文字而不是數組建構子可能更方便。

等效查詢1：

SELECT ts.* 
FROM   task ts
JOIN   LATERAL (
  SELECT ARRAY (
     SELECT tg.tag
     FROM   task_tag tt
     JOIN   tag      tg USING (tag_id)
     WHERE  tt.task_id = ts.task_id
     ) AS tags 
  ) tt ON tt.tags @> '{tag1, tag3}'::text[];

但這仍然是一種浪費。儘管無論如何都必須匯總所有行，LATERAL但弊大於利。

等效查詢2：

SELECT ts.* 
FROM   task ts
JOIN  (
  SELECT task_id
  FROM   task_tag
  GROUP  BY 1
  HAVING array_agg(tag_id) @> '{1, 3}'::int[];
  ) AS tags USING (task_id);

如您所見，我不是在建構標籤數組，只是tag_id. 效率更高。在將輸入標籤傳遞給查詢之前，將它們解析為 ID。

比以前快得多，但對於非平凡大小的表仍然非常低效。

2.整個做法效率很低

上述方法不能使用索引。動態生成的數組確實適用於索引。謂詞出現在聚合之後。

2.1MATERIALIZED VIEW用於只讀（或大部分）表

要使其與索引一起使用，您必須在MATERIALIZED VIEW每個task_id. 以及數組列上的 GIN 索引。僅適用於只讀（或大部分）表，因為 MV 只是一個快照，很快就會因寫入基礎表而過時。

有關的：

• PostgreSQL 可以索引數組列嗎？

MATERIALIZED VIEW：_

CREATE MATERIALIZED VIEW task_tags_mv AS
SELECT task_id, array_agg(tag_id) AS tag_ids
FROM   task_tag
GROUP  BY 1;

當然，我們tag_id再次使用，而不是原始標籤。對於**integer**數組，附加模組提供了更快、更專業的運算符類和索引intarray。有關的：

• smallint 上的 GIN 索引$$ $$未使用列或錯誤“操作員不是唯一的”

所以我們使用這個專門的索引：

CREATE INDEX task_tags_mv_arr_idx ON task_tags_mv USING GIN(tag_ids gin__int_ops);

這個查詢：

SELECT ts.* 
FROM   task ts
JOIN   task_tags_mv mv USING (task_id)
WHERE  mv.tag_ids @> '{1, 3}'::int[];  -- uses intarray operator

這很快，現在。相同的設置適用於匹配任何標籤，使用重疊運算符**&&**而不是包含運算符：

...
WHERE  mv.tag_ids && '{1, 3}'::int[];  -- uses intarray operator

2.2 通用查詢

否則，您需要可以使用索引的完全不同的查詢技術。Yper*在他的評論中指出了一個關於 SO 的相關問題：

• 如何過濾 SQL 結果中的 has-many-through 關係

您的具體要求是簡單地為通用查詢提供標籤列表。

遞歸 CTE 是一種沒有動態 SQL 的方法。（還有很多其他的。）VARIADIC為方便起見，嵌套在帶參數的 SQL 函式中：

CREATE OR REPLACE FUNCTION f_tasks_with_tags(VARIADIC _tags text[])
 RETURNS SETOF task AS
$func$
  WITH RECURSIVE cte AS (
     SELECT task_id, 1 AS idx
     FROM   task_tag
     WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[1])

     UNION
     SELECT task_id, c.idx + 1
     FROM   cte c
     JOIN   task_tag tt USING (task_id)
     WHERE  tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[c.idx + 1])
     )
  SELECT t.*
  FROM   cte  c
  JOIN   task t USING (task_id)
  WHERE  c.idx = array_length(_tags, 1)
$func$  LANGUAGE sql STABLE;

稱呼：

SELECT * FROM f_tasks_with_tags('tag1', 'tag3'); -- add any number of tags

關於VARIADIC（點擊連結了解更多）：

• 在單個參數中傳遞多個值

根據值頻率，它有助於性能（很多）首先傳遞稀疏標籤。儘早消除不合格的任務會更便宜。

另一種可能更快的方法：動態 SQL。建構並執行如下查詢：

SELECT t.*
FROM  (
  SELECT task_id
  FROM   task_tag t1
  JOIN   task_tag t2 USING (task_id)
-- JOIN   task_tag t3 USING (task_id)
-- more ...
  WHERE  t1.tag_id  = 1
  AND    t2.tag_id  = 3
-- AND    t3.tag_id  = 789
-- more ...
  ) tt
JOIN  task t USING (task_id);

在這個查詢中，Postgres 可以自動優化連接序列以首先評估稀疏標籤 - 最多可評估join_collapse_limit表。看：

• Postgres JOIN 條件與 WHERE 條件

在這個站點上有許多用於在 plpgsql 函式中動態建構查詢的程式碼範例。嘗試搜尋。

SQL Fiddle（也有 intarray 模組）。

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/194269