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
Brian Vuyk wrote:
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;
You might find something like this easier to maintain (think about an array, which could also map the levels): SELECT DISTINCT u.uid, ur.rid FROM {users} u RIGHT JOIN {users_roles} ur ON ur.uid = u.uid WHERE rid IN (6, 8, 5, 7, 4) GROUP BY uid; Nancy E. Wichmann, PMP Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
On Nov 23, 2009, at 10:18 AM, Brian Vuyk wrote:
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;
Brian
1) Why are you doing a join when all the info you are selecting is in the users_roles table? If you don't need it for some other reason, problem solved. 2) I've found it's generally better to use subqueries (where X in (select Y from Z)) rather than join and group. - Ken Winters
Brian Vuyk wrote:
1.) We are including extra data from the users table (wasn't central to the question, so I omitted it to simplify) 2.) How would you structure this exactly with subquery? Create a second table where the role id's have a weight for their capability level
On Mon, Nov 23, 2009 at 8:51 AM, Brian Vuyk <brian@brianvuyk.com> wrote:
Jeff Greenberg wrote:
Brian Vuyk wrote:
1.) We are including extra data from the users table (wasn't central to the question, so I omitted it to simplify) 2.) How would you structure this exactly with subquery?
Create a second table where the role id's have a weight for their capability level
Ah, right, I get it now!
There's a module for that - http://drupal.org/project/role_weights #rule35 Cheers, Greg -- Greg Knaddison | 303-800-5623 | http://growingventuresolutions.com Mastering Drupal - http://www.masteringdrupal.com
select * from users where uid in (select uid from users_roles where rid in (4,5,6,7,8)); Note that I dropped the rid column from the results, but your query didn't provide all that useful results for it to start with (picked one). If you actually need it then you may still need a join, and also might need something like group_concat. - Ken Winters On Nov 23, 2009, at 10:41 AM, Brian Vuyk wrote:
1.) We are including extra data from the users table (wasn't central to the question, so I omitted it to simplify) 2.) How would you structure this exactly with subquery?
Brian
Ken Winters wrote:
On Nov 23, 2009, at 10:18 AM, Brian Vuyk wrote:
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;
Brian
1) Why are you doing a join when all the info you are selecting is in the users_roles table? If you don't need it for some other reason, problem solved. 2) I've found it's generally better to use subqueries (where X in (select Y from Z)) rather than join and group.
- Ken Winters
Also be aware that, at least on MySQL, subqueries in a WHERE clause get executed for every otherwise-matching record. Depending on your dataset that could eat up a lot of time. Subqueries in a SELECT or FROM clause are only executed once, so those are fine. With a proper join against a weighting table, though, the subquery shouldn't even be necessary. --Larry Garfield Ken Winters wrote:
select * from users where uid in (select uid from users_roles where rid in (4,5,6,7,8));
Note that I dropped the rid column from the results, but your query didn't provide all that useful results for it to start with (picked one). If you actually need it then you may still need a join, and also might need something like group_concat.
- Ken Winters
On Nov 23, 2009, at 10:41 AM, Brian Vuyk wrote:
1.) We are including extra data from the users table (wasn't central to the question, so I omitted it to simplify) 2.) How would you structure this exactly with subquery?
Brian
Ken Winters wrote:
On Nov 23, 2009, at 10:18 AM, Brian Vuyk wrote:
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;
Brian
1) Why are you doing a join when all the info you are selecting is in the users_roles table? If you don't need it for some other reason, problem solved. 2) I've found it's generally better to use subqueries (where X in (select Y from Z)) rather than join and group.
- Ken Winters
On Mon, 2009-11-23 at 10:18 -0500, Brian Vuyk wrote:
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;
If the order is an arbitrary order, you'll have somewhere to create a new table, containing rid and an order column, then join it with rid in your request, and finally order with the order field. SELECT DISTINCT(u.uid), ynst.rid FROM {users} JOIN {users_roles} ur ON ur.uid = u.uid JOIN {you_new_shiny_table} ynst ON nst.rid = ur.rid ORDER BY ynst.order DESC GROUP BY u.uid If this does not works (I did not tested it, I think the GROUP BY clause will override the ORDER BY one, maybe with more than one ON clause it may work) try a subquery in the result clause: SELECT DISTINCT(u.uid), (SELECT ynst.rid FROM {you_new_shiny_table} ynst WHERE r.rid = ur.rid ORDER BY ynst.order DESC LIMIT 1 ) AS rid FROM {users} JOIN {users_roles} ur ON ur.uid = u.uid GROUP BY u.uid Note: you can even easily create somewhere a form to order your roles (or altering the existing one) to add a dragtable to order them. Pierre.
PS: Please do standard SQL (in previous post, mine was, I think). Re PS: Do not rely on MySQL performance, they are sometime even better when you disable MySQL internal optimizer :D This was a FLAAAAAAAAAAAAAAAAAAAAAAME, Pierre.
participants (7)
-
Brian Vuyk -
Greg Knaddison -
Jeff Greenberg -
Ken Winters -
larry＠garfieldtech.com -
nan wich -
Pierre Rineau