SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Concat but added a new column

    Hi, I have this problem.

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

    PHP Code:
    '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:

    PHP Code:
    +----------+------------+
    |  
    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.
    I Dunno LOL \(_o)/

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    '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.
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh hey thanks oddz!! It works!
    And I thought its gonna be something complex like adding union

    Really appreciate your help!
    I Dunno LOL \(_o)/

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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.
    Oh ok thanks oddz for the kind advise. I will amend it as advise.
    I Dunno LOL \(_o)/


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •