1

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),
  );
0

1 Answer 1

1

So, as I understand, the input to the query is:

  • a group name;
  • a user name.

And the output should be the list of all the articles belonging to the given group provided either of the following is true:

  • the group is open;
  • the given user belongs to the group.

If that is correct, I believe the missing condition could be implemented as an EXISTS predicate:

...
    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'
    )
...

The join to membership in the main SELECT would then be redundant.

Alternatively, you could outer-join the result of membership INNER JOIN myuser and check for presence of a match in the WHERE clause, like this:

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)
  )
;

An EXISTS predicate is often faster than an equivalent join, though, so you might prefer the former option, but test in your environment to see which is actually better.

If the user is specified by ID instead of a name, the query becomes simpler in each case. The EXISTS predicate would look like this:

EXISTS
(
  SELECT
    *
  FROM
    membership AS m
  WHERE
    m.mygroup_id = g.id
    AND m.myuser_id = <input-user-ID>
)

For the second option, using an ID instead of a name to specify the user would mean a simpler left join:

LEFT JOIN membership AS m
  ON  m.mygroup_id = g.id
  AND m.myuser_id = <input-user-ID>

And, of course, you would need to replace the u.id reference in the WHERE clause with a reference to a non-nullable membership column. The m.id would do, so:

WHERE 
  (g.name = 'FOO-BAR') 
  AND
  (
    (g.open IS TRUE) 
    OR
    (m.id IS NOT NULL)
  )
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.