[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