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

Groups > comp.databases.postgresql > #240

Re: logic of limiting choices and forcing choices

Show all headers | View raw

Am 09.09.2011 11:02, schrieb Laurenz Albe:
> 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 
> 
> 

Thank you, it works fine. I should have included the ddl

/Str.

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