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

Re: Aggregate

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: David Wheeler <david(at)kineticode(dot)com>, sfpug(at)postgresql(dot)org
Subject: Re: Aggregate
Date: 2003-03-03 21:33:21
Message-ID: 200303031333.21941.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
David,

> 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.
<snip>
> 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?

Try this:

          SELECT DISTINCT a.id
          FROM   site a, member m, site_member c, member m3, member m4,
                 site_member c3, user_member um,
                 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
                 AND NOT EXISTS ( select id from grp_priv gp4
			WHERE  gm.grp_priv__id = gp4.id
				 AND gp4.value        =  4) 
          ORDER BY a.id

... though I'm a little baffled by your join structure, so I'm not sure the 
crieteria in the EXISTS clause are right.

You should also  make sure that your GEQO threshold on your server is set 
higher than the default.  I usually set mine to 24.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


In response to

  • Aggregate at 2003-03-03 20:45:00 from David Wheeler

Responses

sfpug by date

Next:From: David WheelerDate: 2003-03-04 03:20:44
Subject: Re: Aggregate
Previous:From: Stephan SzaboDate: 2003-03-03 21:08:48
Subject: Re: Aggregate

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