Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #236

Re: logic of limiting choices and forcing choices

Show all headers | View raw

M. Strobel wrote:
> I just can not find the set based sql approach to the following
> application logic:
>
> I have a table of choices with id and text, say:
> 1 coffee
> 2 tea
> 3 water
> 4 juice
>
> Now some users have restrictions, for example user 1 is
> disallowed coffeine. Some Users are forced one choice - user 2 is
> only allowed water, so there is another type of restriction.
>
> This would yield table choicerestrictions (userid, choiceid, tor):
> 1 2 exclude
> 1 2 exclude
> 2 3 force
>
> Is this a known application pattern I can read about somewhere?
>
> How could I get a result set in one select? This would be far
> better than doing it in the application.
>
> Table design: the force entry in choicerestrictions would be
> equivalent to exclude choices 1,2 and 4. I prefer a force entry,
> because it states the logic, and is better when you insert 5 milk
> into choices.

Here is DDL for your example:

CREATE TABLE users (
   userid integer PRIMARY KEY,
   username text NOT NULL
);

INSERT INTO users VALUES (1, 'one');
INSERT INTO users VALUES (2, 'two');
INSERT INTO users VALUES (3, 'three');

CREATE TABLE choices (
   choiceid integer PRIMARY KEY,
   choicename text NOT NULL
);

INSERT INTO choices VALUES (1, 'coffee');
INSERT INTO choices VALUES (2, 'tea');
INSERT INTO choices VALUES (3, 'water');
INSERT INTO choices VALUES (4, 'juice');

CREATE TYPE restriction AS ENUM ('exclude', 'force');

CREATE TABLE choicerestrictions (
   userid integer NOT NULL REFERENCES users(userid),
   choiceid integer NOT NULL REFERENCES choices(choiceid),
   tor restriction NOT NULL,
   PRIMARY KEY (userid, choiceid)
);

INSERT INTO choicerestrictions VALUES (1, 1, 'exclude');
INSERT INTO choicerestrictions VALUES (1, 2, 'exclude');
INSERT INTO choicerestrictions VALUES (2, 3, 'force');


A valid way of selecting all possible (username, choicename)
pairs would be:

(SELECT u.username, c.choicename
   FROM (SELECT username, r.choiceid
         FROM choices c
            JOIN choicerestrictions r ON
               (c.choiceid = r.choiceid AND r.tor = 'force')
            RIGHT OUTER JOIN users USING (userid)
        ) u
      JOIN choices c ON
         (u.choiceid = c.choiceid OR u.choiceid IS NULL))
EXCEPT (SELECT u.username, c.choicename
   FROM users u
      JOIN choicerestrictions r USING (userid)
      JOIN choices c USING (choiceid)
   WHERE tor = 'exclude');

I don't claim that it is the most efficient way possible...

Yours,
Laurenz Albe 

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

logic of limiting choices and forcing choices "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-09-08 11:58 +0200
  Re: logic of limiting choices and forcing choices "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-09 11:02 +0200
    Re: logic of limiting choices and forcing choices "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-09-10 12:10 +0200
    Re: logic of limiting choices and forcing choices "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-09-10 12:30 +0200

csiph-web