Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!fu-berlin.de!uni-berlin.de!not-for-mail
From: "M. Strobel" <sorry_no_mail_here@nowhere.dee>
Newsgroups: comp.databases.postgresql
Subject: Re: logic of limiting choices and forcing choices
Date: Sat, 10 Sep 2011 12:10:20 +0200
Lines: 93
Message-ID: <9d0rccFonbU1@mid.uni-berlin.de>
References: <9crhv3F4klU1@mid.uni-berlin.de> <1315558978.338010@proxy.dienste.wien.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Trace: news.uni-berlin.de yRpVwt1jAjJL2N4Mv7qwFwqYAmSNp9SC8wIov1m3sUOfxpMAk=
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.2.20) Gecko/20110804 SUSE/3.1.12 Lightning/1.0b2 Thunderbird/3.1.12
In-Reply-To: <1315558978.338010@proxy.dienste.wien.at>
Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:240

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.