Posted by Michael-IDA on Thu, Aug 06, 2020 - 7:29am --- Updated: Aug 06 '20 - 1:53pm

I have this alias for Drupal, which I can convert to Brush (working okay), but it's borking on no role table:

alias lastaccess='drush sql-query "SELECT u.uid AS UID, from_unixtime(u.access, '\''%m/%d/%y %H:%i:%s'\'') AS '\''Last Access      '\'', RPAD(u.name,15,'\'' '\'') AS '\''Name      '\'',  SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT r.name ORDER BY r.rid SEPARATOR '\'', '\''), '\'','\'', 3) AS Roles FROM users AS u LEFT JOIN users_roles AS ur ON ur.uid = u.uid LEFT JOIN role AS r ON r.rid = ur.rid GROUP BY u.uid ORDER BY access DESC LIMIT 5"'

Output in Drupal is:

UID     Last Access             Name            Roles
229     08/05/20 12:22:57       adfgovich1      subscriber
14      07/29/20 13:01:43       sadfster9       affiliate
230     07/29/20 11:51:55       bkvhs1          tester
77      07/07/20 20:04:13       Michaelasdf     administrator, coordinator
74      06/18/20 14:27:54       aiovbb1062      administrator, coordinator

Any existing guides on how to convert that into the Backdrop config stuff for roles no longer having a table?

Thanks,

Michael

Edit:
As what I pasted was from an alias (with all the extra escaped single quotes), hosef’s conversion below (accepted answer) probably won’t run as straight SQL.  As such I cleaned it up and also removed role ordering, as role weights are now in the config file, so ordering isn’t really useful.  End result is:

alias lastaccess='brush sql-query "SELECT u.uid AS UID, from_unixtime(u.access, '\''%m/%d/%y %H:%i:%s'\'') AS '\''Last Access      '\'', RPAD(u.name,15,'\'' '\'') AS '\''Name      '\'',  SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT ur.role SEPARATOR '\'', '\''), '\'','\'', 3) AS Roles FROM users AS u LEFT JOIN users_roles AS ur ON ur.uid = u.uid GROUP BY u.uid ORDER BY access DESC LIMIT 5"'

PS:  It's still an alias, so to run as straight SQL remove all the escaped single quotes, which is a pita I know ;)

Accepted answer

In Backdrop the role ID is the machine name of the role now. In your query you can get the name by using GROUP_CONCAT on user_roles.role.

This should be close to what you currently have for Drupal.

SELECT
  u.uid AS UID,
  from_unixtime(u.access, '\''%m/%d/%y %H:%i:%s'\'') AS '\''Last Access      '\'',
  RPAD(u.name,15,'\'' '\'') AS '\''Name      '\'',
  SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT ur.role ORDER BY ur.role SEPARATOR '\'', '\''), '\'','\'', 3) AS Roles
FROM users AS u
LEFT JOIN users_roles AS ur ON ur.uid = u.uid
GROUP BY u.uid
ORDER BY access DESC
LIMIT 5

Comments

Thu, Aug 06, 2020 - 10:25am by hosef

In Backdrop the role ID is the machine name of the role now. In your query you can get the name by using GROUP_CONCAT on user_roles.role.

This should be close to what you currently have for Drupal.

SELECT
  u.uid AS UID,
  from_unixtime(u.access, '\''%m/%d/%y %H:%i:%s'\'') AS '\''Last Access      '\'',
  RPAD(u.name,15,'\'' '\'') AS '\''Name      '\'',
  SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT ur.role ORDER BY ur.role SEPARATOR '\'', '\''), '\'','\'', 3) AS Roles
FROM users AS u
LEFT JOIN users_roles AS ur ON ur.uid = u.uid
GROUP BY u.uid
ORDER BY access DESC
LIMIT 5

Thu, Aug 06, 2020 - 1:56pm by Michael-IDA

Thank you hosef!

Sad part is I even looked at the users_roles before I wandered off into the configuration files area...

Best,
Michael