Skip site navigation (1) Skip section navigation (2)

Aggregate

From: David Wheeler <david(at)kineticode(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Aggregate
Date: 2003-03-03 20:45:00
Message-ID: 005AFD13-4DB9-11D7-B98B-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
Hi All,

I need a little advice from the SQL pros on the list. I have this query 
in Bricolage:

          SELECT DISTINCT a.id
          FROM   site a, member m, site_member c, member m3, member m4,
                 site_member c3, user_member um,grp_priv gp,
                 grp_priv__grp_member gm
          WHERE  a.id = c.object_id
                 AND c.member__id    = m.id
                 AND m.active        = 1
                 AND a.id            = c3.object_id
                 AND c3.member__id   = m3.id
                 AND m3.active       = 1
                 AND m3.grp__id      = gm.grp__id
                 AND gm.grp_priv__id = gp.id
                 AND gp.value        < 4
                 AND gp.grp__id      = m4.grp__id
                 AND m4.id           = um.member__id
                 AND um.object_id    = 1
          ORDER BY a.id

It is designed to get a list of site IDs for a given user ID. It does 
this by going through Bricolage's group-based permissions system. The 
complexity of the join isn't what I'm worried about (I think that it's 
relatively compact).

The problem I have is with "gp.value < 4". You see, permission value 4 
is DENY, and anything below that is CREATE (3), EDIT (2), or READ (1). 
But this query could return *many* rows with many different permissions 
associated with one site. The issue is that if *any* permission value 
for a particular Site ID is 4 (DENY), then I need it to return *no* 
rows for that site.

So, to simplify, a site can have many permissions. If I just had one 
table matching site IDs to permission values, the query that would do 
what I want might look like this:

       SELECT DISTINCT site__id
       FROM   site__permission
       WHERE  value < 4
              AND site__id NOT IN (
                  SELECT site__id
                  FROM   site__permission
                  WHERE  value = 4
              )

The question is, how can I alter the first query above to get that same 
behavior -- preferably while avoiding the performance overhead of a NOT 
IN statement?

TIA for the help!

David

-- 
David Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394
                                                Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


Responses

sfpug by date

Next:From: Stephan SzaboDate: 2003-03-03 21:06:29
Subject: Re: Aggregate
Previous:From: David WheelerDate: 2003-02-27 17:44:38
Subject: Re: DBD::pg error trapping?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group