-1

Given the following script:

SELECT 
    category AS MODULE,
    acl_roles.name as ROLE_NAME
     ,GROUP_CONCAT(
        CASE aclaccess 
            WHEN 89 THEN 'E' 
            WHEN 0 THEN 'N' 
            WHEN -98 THEN 'D' 
            WHEN 90 THEN 'A' 
            WHEN 75 THEN 'O' 
            WHEN -99 THEN 'N' 
        END 
      SEPARATOR ', ') AS DEFAULT_ACCESS_WEB
      ,GROUP_CONCAT(
        CASE access_override 
        WHEN 89 THEN 'E' 
        WHEN 0 THEN 'N' 
        WHEN -98 THEN 'D' 
        WHEN 90 THEN 'A' 
        WHEN 75 THEN 'O' 
        WHEN -99 THEN 'N' 
        END 
      SEPARATOR ', ') AS OVERRIDE_ACCESS_WEB
      ,GROUP_CONCAT(
        acl_actions.name ORDER BY FIELD(acl_actions.name, "access", "delete", "edit", "export", "import", "list", "view") 
        SEPARATOR ', ') AS ACTION_NAME_ORDER
FROM `acl_roles_actions`
    JOIN `acl_actions` ON acl_actions.id = acl_roles_actions.action_id
    JOIN `acl_roles` ON acl_roles_actions.role_id = acl_roles.id
WHERE `role_id` in ('SOME_HASH', 'ANOTHER_HASH') AND `action_id` IN
 (SELECT `id` FROM `acl_actions` WHERE `category` LIKE '%SOMENAME%')
 GROUP BY ROLE_NAME
 ORDER BY ROLE_NAME, FIELD(acl_actions.name, "access", "delete", "edit", "export", "import", "list", "view")

I need to have the DEFAULT_ACCESS_WEB and OVERRIDE_ACCESS_WEB results adhere to the same ordering as the ACTION_NAME_ORDER.

The ACTION_NAME_ORDER is appearing in the correct order in the results, but I'm unable to get the same ORDER BY FIELD(acl_actions.name, "access", "delete", "edit", "export", "import", "list", "view") working within the first 2 GROUP_CONCAT's.

Do they need to be nested within extra SELECTs to have a context of the field I want to order them by?

1

It turned out, I needed to put the ORDER BY FIELD after the END of the CASE:

SELECT
    category AS MODULE,
    acl_roles.name as ROLE_NAME
     ,GROUP_CONCAT(
        CASE aclaccess
            WHEN 89 THEN 'E'
            WHEN 0 THEN 'N'
            WHEN -98 THEN 'D'
            WHEN 90 THEN 'A'
            WHEN 75 THEN 'O'
            WHEN -99 THEN 'N'
        END ORDER BY FIELD(acl_actions.name, "access", "delete", "edit", "export", "import", "list", "view") 
      SEPARATOR ', ') AS DEFAULT_ACCESS_WEB
      ,GROUP_CONCAT(
        CASE access_override
        WHEN 89 THEN 'E'
        WHEN 0 THEN 'N'
        WHEN -98 THEN 'D'
        WHEN 90 THEN 'A'
        WHEN 75 THEN 'O'
        WHEN -99 THEN 'N'
        END ORDER BY FIELD(acl_actions.name, "access", "delete", "edit", "export", "import", "list", "view") 
      SEPARATOR ', ') AS OVERRIDE_ACCESS_WEB
      ,GROUP_CONCAT(
        acl_actions.name ORDER BY FIELD(acl_actions.name, "access", "delete", "edit", "export", "import", "list", "view")
        SEPARATOR ', ') AS ACTION_NAME_ORDER
FROM `acl_roles_actions`
    JOIN `acl_actions` ON acl_actions.id = acl_roles_actions.action_id
    JOIN `acl_roles` ON acl_roles_actions.role_id = acl_roles.id
WHERE `role_id` in ('SOME_HASH', 'ANOTHER_HASH') AND `action_id` IN
 (SELECT `id` FROM `acl_actions` WHERE `category` LIKE '%SOMENAME%')
 GROUP BY ROLE_NAME
 ORDER BY ROLE_NAME, FIELD(acl_actions.name, "access", "delete", "edit", "export", "import", "list", "view")
 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.