Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

WordPress Multisite - Sorting by user role on admin user table WordPress

  • SOLVED

Hi,

I want to sort by user role on the admin user table but can't work out how to achieve this? It is a multisite installation.

Thanks,
Chris.

Answers (2)

2014-12-23

Dbranes answers:

There are few problems with this idea, for example:

- each user can have multiple roles, it's not clear how ordering should work

- roles are stored in a serialized array in the database


Josh Cranwell comments:

Hmmm, well it would be the primary role or could just settle with the main user role. Serialized values - how annoying!


Dbranes comments:

yes, these serialized arrays can be annoying ;-)

You might add a new user meta for each user, to indicate the main role.

Then you can modify the <em>WP_User_Query</em> with the <em>pre_user_query</em> hook, for example.


Josh Cranwell comments:

OK so I now have a meta key for each user... for example domain_prefix_user_role = 'the_role'.

How could I extend my query to work that out?

Thanks.


Dbranes comments:

ok, then try for example:

add_action( 'pre_get_users', function ( $q ) {
if( is_admin() && 'meta_value' === $q->query_vars['orderby'] ) {
$q->query_vars['meta_key'] = 'domain_prefix_user_role'; // <-- Modify this to your needs!
}
});


where you activate it through:

/wp-admin/network/users.php?orderby=meta_value&order=asc


ps: you might need to restrict this further to the specific page where you want this to run.


Josh Cranwell comments:

Ah that did the job!

Basically what I did was on registration, set a meta key against the user that stores their role and used the 'set_user_role' WordPress filter to handle any future role changes to update the postmeta (as we only use individual roles on our site so no issue of double roles).

Appreciate your help and have a good Christmas!


Dbranes comments:

ok great,

Notice that by using the <em>pre_get_users</em> with a <em>meta_key</em> set,

you get the usermeta join for free,

so there's no need to add it by hand from the <em>pre_user_query</em> hook.

Happy holidays

2014-12-23

Romel Apuya answers:

please take a look at this

[[LINK href="http://wordpress.stackexchange.com/questions/11403/mysql-query-two-database-tables-users-and-usermeta-and-sort-by-custom-meta-int"]]http://wordpress.stackexchange.com/questions/11403/mysql-query-two-database-tables-users-and-usermeta-and-sort-by-custom-meta-int[[/LINK]]


Josh Cranwell comments:

Hi,

Thanks for sending this but I need it for the admin of my site - this to me seems much more front-end based.

Chris.


Romel Apuya comments:

I think there is no way to sort by admin table since users can only be found in the wp_users table.
users are distinguished by the 'role' field.

what you might need is get all the users and filter by roles and in the roles with admin you can sort the users

like this example here

[[LINK href="http://bitsandbabble.com/2014/12/make-wp_user_query-sort-by-role/"]]http://bitsandbabble.com/2014/12/make-wp_user_query-sort-by-role/[[/LINK]]


Romel Apuya comments:

if the above wont work

try this sql query


SELECT
ID, user_email, user_login,
first_name.meta_value as first_name,
last_name.meta_value as last_name,
phone_number.meta_value as phone_number,
wp_capabilities.meta_value as wp_capabilities
FROM wp_users
JOIN wp_usermeta AS wp_capabilities ON wp_capabilities.user_id=ID
AND wp_capabilities.meta_key='wp_capabilities'
LEFT JOIN wp_usermeta AS first_name ON first_name.user_id=ID
AND first_name.meta_key='first_name'
LEFT JOIN wp_usermeta AS last_name ON last_name.user_id=ID
AND last_name.meta_key='last_name'
LEFT JOIN wp_usermeta AS phone_number ON phone_number.user_id=ID
AND phone_number.meta_key='phone_number'
WHERE
wp_capabilities.meta_value LIKE '%administrator%'
ORDER BY
wp_capabilities


Josh Cranwell comments:

Thanks Romel - my solution in the end was to use pre_user_query to setup the relevant joins so with a combination of your help and Dbranes I was able to resolve my issue fully.

// create a left join so we can sort by user_role meta key
$this->filter('pre_user_query', function ($query) {

// set our globals
global $wpdb;

// make sure we are in the admin
if(!is_admin()) return;

if('custom_role' == $query->get('orderby')) {

// update our query
$query->query_from .= sprintf(
" LEFT JOIN %1\$s m13 ON (%2\$s.ID = m13.user_id AND m13.meta_key = '%3\$s') ",
$wpdb->usermeta,
$wpdb->users,
$wpdb->prefix . 'user_role'
);

// add orderby
$query->query_orderby = "ORDER BY m13.meta_value " . $query->get('order');

}

return $query;

});


Have a good Christmas!