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

Groups > comp.databases.postgresql > #235 > unrolled thread

logic of limiting choices and forcing choices

Back to article view | Back to comp.databases.postgresql

Contents

  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

#235 — logic of limiting choices and forcing choices

Hi,

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.

Thanks for your comments.

/Str.

[toc] | [next] | [standalone]

#236

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 

[toc] | [prev] | [next] | [standalone]

#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.

[toc] | [prev] | [next] | [standalone]

#241

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

Hey, it mimics a procedural approach. I would have selected the
force entry, if there is none select all choices omitting the
exclude-entries.

Just could not get it into one query.

/Str.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web