[development] Creative querying

[Brian Vuyk]

Hello all.

I am trying to write a MySQL query for users and roles, and could use a 
little help along.

Basically, I need to compile a list of users who have certain roles. On 
this site, users can have 4-5 roles which represent ascending privilege 
levels, and we need to find the highest privilege level a user can get.

The problem is, the highest privilege level in terms of functionality 
isn't the highest numerically by role id.

In essence, as output, I am trying to get results that show a user id in 
one column, and rid 6 if they have it, otherwise rid 8, otherwise rid 5, 
otherwise 7, otherwise 4.

Edit:

As I was typing this, I thought of another thing to try, and it seems to 
have worked:

SELECT DISTINCT u.uid, ur.rid FROM {users} u RIGHT JOIN {users_roles} ur 
ON ur.uid = u.uid WHERE rid = 6 OR rid = 8 OR rid = 5 OR rid = 7 OR rid 
= 4 GROUP BY uid;

This depends on MySQL's query evaluation to stop on the first match from 
the OR'd conditions that apply. Without the GROUP BY condition, this 
still returns a list of all uid / rid combinations...

Since this email was already written, I thought I would send it along 
with the solution I have both so that:

1. If anyone else is trying to do something similar, they can see what I 
did.
2. It goes into the list archives for the same reason as #1.

Brian