[thelist] Complex SQL problem. Groups and rules
Ken Schaefer
Ken at adOpenStatic.com
Fri Feb 25 20:41:54 CST 2005
I don't really know what your question is.
Can you supply some sample data, and the expected output?
Cheers
Ken
: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Richard Livsey
: Sent: Saturday, 26 February 2005 5:23 AM
: To: thelist at lists.evolt.org
: Subject: [thelist] Complex SQL problem. Groups and rules
:
: I'm working on bit of semi complicated SQL that I can't get my head
: round. Apologies for the rambling nature of this email, this is a bit of
: a brain dump!
:
: Firstly a bit of background.
:
: We have a bunch of groups, and these groups have rules that users must
: meet in order for them to see them.
: I'm trying to write a query that will return all the groups that a
: specific user is eligible to join.
:
: The group may have any number of rules, or none at all (any user can
: join it).
:
: A rule is an entry in a lookup table (of which we have 4). For this we
: have 4 rules - boroughs, wards, roles and sics.
: And address has a borough and a ward, a user has a role and an sic.
:
: Now a user may have a number of different addresses and the rules can
: only apply to one of their addresses, IE if there is a rule to say that
: the address must be in borough 'a' and ward 'b' and you have 2 addresses:
:
: address1
: - borough 'a'
: - ward 'y'
:
: address2
: - borough 'z'
: - ward 'b'
:
: Then this user can't join the group. However if they had an address like
: this then they could join:
:
: address
: - borough 'a'
: - ward 'b'
:
: To make this even more complicated, a groups rules can be applied as
: 'AND' or 'OR'. IE 'users must match all rules' or 'users can match any
: rules'.
:
: Think it may take a couple of queries (at least) to work this one out..
: but so far I've not been able to get my head round it.
:
: So as an example, a group set of rules could be something like:
:
: from as simple as:
:
: users who have role 'x'
: users who are in borough 'a'
:
: to ones as complex as:
:
: users who are in borough 'a' AND ward 'b' AND have role 'c' AND sic 'd'
: users who are in borough 'a' OR borough 'b' OR have role 'c'
:
: (note that we don't mix AND and OR which makes it slightly less
: complicated to work out)
:
: Here are the relevant tables:
:
: addresses
: - id
: - userId
: - boroughId
: - wardId
:
: users
: - id
: - roleId
: - sicId
:
: groups
: - id
: - name
: - combination (this is an enum or 'AND' and 'OR')
:
: groupBoroughs
: - boroughId
: - groupId
:
: groupRoles
: - roleId
: - groupId
:
: groupWards
: - wardId
: - groupId
:
: groupSics
: - sicId
: - groupId
:
: On top of that, we then should only show groups that the user is not
: already a member of. But I'll worry about that later and should be
: simple compared to the rest of it!
: Members are stored in the groupMembers table.
:
: groupMembers
: - userId
: - groupId
:
: Ugh.. anyway - any pointers would be greatly appreciated as I've been
: getting kicked about with this one for a few hours now and don't seem to
: be making much headway.
: Also - any alternative database layouts which would make the query
: easier would also be great.
More information about the thelist
mailing list