[development] Creative querying

[Pierre Rineau]

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.