The Scenario
You're writing code to perform authentication for a web-based app. Your users live in one table (Site_User), your list of site permissions live in another table (systemRoles), and the two are related via a junction table (jctUserRole) that manages the many to many relationship there. Your backend database: MySQL version 5 or better.
So, in an ideal world, you will be able to write a single query that will both authenticate the user AND retrieve a comma delimited list of their roles contained in a single field; if their credentials are good, your result will only have a single record in it. Typically, however, because of the way many to many relationships are normalized, the "one query" you want will be returning multiple rows...one row for each permission the authenticating user is related to. For example, here is the typical way one would write the authenticating query:
FROM Site_User u
INNER JOIN jctUserRole j ON j.userID = u.id
INNER JOIN systemroles r ON r.id = j.roleID
WHERE u.username = 'username'
AND u.password = 'password'
This produces a result that looks like this:
We would then be required to write additional code in order to more properly "package" up this user's list of permissions for use within the system.
But enter the wonderful world of MySQL 5, and the 'group_concat' function. Group_concat allows you to concatenate values into a list based on a specified grouping, and will produce a list of unique values delimited by whatever character you choose (but defaults to a comma). So transforming the above query just a little bit, we get:
FROM Site_User u
INNER JOIN jctUserRole j ON j.userID = u.id
INNER JOIN systemroles r ON r.id = j.roleID
WHERE r.username = 'username'
AND r.password = 'password'
GROUP BY u.id, u.firstname, u.lastname
And a result set that now looks like this:
Man, SO much cleaner and nicer, eh? Now you can simply treat the "roles" value as a list...explode it into an array if you want, or leverage ColdFusion's myriad of List functions to interact with it.
Since discovering group_concat, I've found several other uses for it as well, particularly when producing reports for my internal clients...it saves me a lot of iterating over result sets in order to produce value summaries.
You can read the documentation for this function here
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.