Concat but added a new column

Hi, I have this problem.

A user can have multiple roles and I have this query working fine.

'SELECT *, u.userID AS userID,
GROUP_CONCAT(r.roleID) as roleIDs,
GROUP_CONCAT(r.title) as roleTitle
FROM user u
INNER JOIN user_role ur ON(ur.userID = u.userID)
INNER JOIN role r ON(r.roleID = ur.roleID)
WHERE u.userID = "1"'

But now I’ve added a enum field on user_role table “isDefault enum(0,1)” to tell me which role is the default role of the user. How do I amend the query so as it will retrieve something like this:


+----------+------------+
|  roleIDs  |  isDefault  |
+----------+------------+
|  1,10,13 |      10       |   // showing roleID 10 is the default
+----------+------------+

// The user_role table structure is like the following:
+----------+----------+------------+
|  userID  | roleIDs  |  isDefault  |
+----------+----------+------------+
|      1   |     1       |      0         | 
+----------+----------+------------+
|      1   |     10     |      0         | 
+----------+----------+------------+
|      1   |     13     |      1         | 
+----------+----------+------------+

I have been trying on union but keeps getting error…Any help greatly appreciated.


'SELECT *, u.userID AS userID,
GROUP_CONCAT(r.roleID) as roleIDs,
GROUP_CONCAT(r.title) as roleTitle,
urd.roleIDs isDefault
FROM user u
INNER JOIN user_role ur ON(ur.userID = u.userID)
INNER JOIN role r ON(r.roleID = ur.roleID)
INNER JOIN user_role urd ON (urd.userID = u.userID AND urd.isDefault = 1)
WHERE u.userID = "1"'

Also, to make sure a user can’t have more than a single default role you may want to consider changing the isDefault column to be a TINYINT accepting a null value. Than define a unique key of: UNIQUE KEY(userID,isDefault). Mark the default role as 1 and make it null for everything else. That will prevent any user from having multiple user_roles rows with 1 in the isDefault column. Why I say this is because in the current design it is possible to have multiple default roles, which doesn’t seem like it should be allowed regardless of application logic that could be preventing it. also, you will be staying away from the evil enum, which while I understand what your trying to do isn’t the preferred way define a boolean value. The best way to do it is with a TINYINT.

Oh hey thanks oddz!! It works!
And I thought its gonna be something complex like adding union :frowning:

Really appreciate your help! :tup::tup:

Oh ok thanks oddz for the kind advise. I will amend it as advise.