Postgresql
如果使用者屬於合格組,則從表中訪問文章
下表是
myuser
&mygroup
,其中每個使用者通過 M2M 表擁有 1 個或多個組的成員資格membership
。如果滿足其中一個條件,則使用者可以訪問文章 1) 文章屬於一個
open
組 2) 文章屬於使用者所屬的組這是我的查詢以選擇一篇文章,給定組名
FOO-BAR
和使用者 IDBAZ-ID
。我無法表達BAZ-ID
應該屬於屬於 group 的使用者 ID 列表的條件FOO-BAR
。PS 使用 PostgreSQL 9.3
SELECT * FROM article a INNER JOIN mygroup g ON g.id = a.mygroup_id INNER JOIN membership m ON m.mygroup_id = g.id WHERE (g.name = 'FOO-BAR') AND ( (g.open is TRUE) OR (?) -- `BAZ-ID` IN a list of m.user_id )
表
CREATE TABLE myuser ( id serial NOT NULL, name text NOT NULL, CONSTRAINT pkey1 PRIMARY KEY (id) ); CREATE TABLE mygroup ( id serial NOT NULL, name text NOT NULL, open boolean NOT NULL, CONSTRAINT pkey2 PRIMARY KEY (id) ); CREATE TABLE membership ( id serial NOT NULL, myuser_id integer NOT NULL, mygroup_id integer NOT NULL, CONSTRAINT pkey3 PRIMARY KEY (id), CONSTRAINT fd1 FOREIGN KEY (myuser_id) REFERENCES myuser (id), CONSTRAINT fd2 FOREIGN KEY (mygroup_id) REFERENCES mygroup (id), CONSTRAINT ukey1 UNIQUE (mygroup_id, myuser_id) ); CREATE TABLE article ( id serial NOT NULL, content integer NOT NULL, mygroup_id integer NOT NULL, CONSTRAINT pkey4 PRIMARY KEY (id), CONSTRAINT fd3 FOREIGN KEY (mygroup_id) REFERENCES mygroup (id), );
因此,據我了解,查詢的輸入是:
- 組名;
- 使用者名。
如果滿足以下任一條件,則輸出應該是屬於給定組的所有文章的列表:
- 該小組是開放的;
- 給定的使用者屬於該組。
如果這是正確的,我相信缺失的條件可以作為 EXISTS 謂詞來實現:
... OR EXISTS ( SELECT * FROM myuser AS u INNER JOIN membership AS m ON u.id = m.myuser_id WHERE m.mygroup_id = g.id AND u.name = 'BAZ-ID' ) ...
主 SELECT 中的連接
membership
將是多餘的。或者,您可以外連接
membership INNER JOIN myuser
WHERE 子句的結果並檢查是否存在匹配項,如下所示:SELECT * FROM article AS a INNER JOIN mygroup AS g ON g.id = a.mygroup_id **LEFT JOIN membership AS m INNER JOIN myuser AS u ON m.myuser_id = u.id ON m.mygroup_id = g.id AND u.name = 'BAZ-ID'** WHERE (g.name = 'FOO-BAR') AND ( (g.open IS TRUE) OR **(u.id IS NOT NULL)** ) ;
但是,EXISTS 謂詞通常比等效連接更快,因此您可能更喜歡前一個選項,但在您的環境中測試以查看哪個實際上更好。
如果使用者是由 ID 而不是名稱指定的,則在每種情況下查詢都會變得更簡單。EXISTS 謂詞如下所示:
EXISTS ( SELECT * FROM membership AS m WHERE m.mygroup_id = g.id AND m.myuser_id = <input-user-ID> )
對於第二個選項,使用 ID 而不是名稱來指定使用者意味著更簡單的左連接:
LEFT JOIN membership AS m ON m.mygroup_id = g.id AND m.myuser_id = <input-user-ID>
而且,當然,您需要將
u.id
WHERE 子句中的引用替換為對不可為空membership
列的引用。m.id
會這樣做,所以:WHERE (g.name = 'FOO-BAR') AND ( (g.open IS TRUE) OR (**m.id** IS NOT NULL) )