Re: Aggregate

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]