The tables below are myuser
& mygroup
, where each user has membership to 1 or more groups via an M2M table membership
.
The articles are accessible by users if one of the condition is satisfied
1) article belongs to an open
group
2) article belongs to a group of which the user is a member
Here's me query to select an article, given group name FOO-BAR
and user id BAZ-ID
. I'm unable to express the condition that BAZ-ID
should belong to list of user id's that are part of group FOO-BAR
.
P.S. Using 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
)
Tables
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),
);