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

Re: Aggregate

From: David Wheeler <david(at)kineticode(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Aggregate
Date: 2003-03-04 03:30:25
Message-ID: A35B5DA8-4DF1-11D7-B98B-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
On Monday, March 3, 2003, at 01:07  PM, David Fetter wrote:

> How 'bout "NOT EXISTS"?

Might have to do another hair join in the subquery. But that might be 
my only option (especially since aggregates are slow, anyway).

> BTW, I'm not quite clear on why you have the biggie self-join thing
> w/member 3 times & site_member twice.  What does it do?

Well, it has to do with permissions. Groups of users are granted 
permissions to groups of objects. For example member is the table for 
group members, and user_member is the corresponding table for user 
objects that are group members. Similarly, site_member is the table for 
site objects that are group members. Permissions are created by a 
relationship between grp_priv and grp_priv__grp_member. grp_priv links 
in user groups and the permission, and grp_priv__grp_member links in 
object groups.

That explains all the m3, c3, gp, and gm stuff. m and c are linked in 
totally separately just so that a query can include group IDs. I use 
this to collect a list of the group IDs that an object is in since 
these serve as a sort of ACL for the object. That query looks like this:

          SELECT a.id , a.name, a.description, a.domain_name, a.active,
                 m.grp__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    = 100
          ORDER BY a.name, a.id

So, was that about as clear as mud?

Regards,

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]


In response to

sfpug by date

Next:From: David WheelerDate: 2003-03-04 03:35:22
Subject: Re: Aggregate
Previous:From: David WheelerDate: 2003-03-04 03:20:44
Subject: Re: Aggregate

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