How to order custom user list columns by datetime?

Question

I am trying to add the ordering options to some custom user list columns I added in the Wordpress admin. This is my code :

function mmm_user_sortable_columns_query( $userquery ){

    if( 'test_date' == $userquery -> query_vars['orderby'] ) {

        global $wpdb;

        $userquery -> query_from .= " LEFT OUTER JOIN $wpdb->usermeta AS alias ON ($wpdb->users.ID = alias.user_id) ";
        $userquery -> query_where .= " AND alias.meta_key = 'test_date' ";
        $userquery -> query_orderby = " ORDER BY alias.meta_value " . ($userquery -> query_vars["order"] == "ASC" ? "asc " : "desc ");

    }

}
add_action( 'pre_user_query', 'mmm_user_sortable_columns_query' );

This doesn't sort properly, because my meta value is not a string but a DATETIME. So I tried to replace query_orderby with :

$userquery -> query_orderby = " ORDER BY CONVERT( datetime, alias.meta_value ) " . ($userquery -> query_vars["order"] == "ASC" ? "asc " : "desc ");

But that doesn't work. And adding

$userquery -> query_where .= " AND alias.meta_type = 'DATETIME' ";

doesn't do it either. What am I missing ?

Answer 1

I think you could simplify this a lot by using the pre_get_users hook, instead of working directly with the complicated SQL within the pre_user_query hook.

Try something like:

add_action( 'pre_get_users', function( \WP_User_Query $q )
{
    $qv = &$q->query_vars;
    if( isset( $qv['orderby'] ) && 'test_date' === $qv['orderby'] )
    {
        // Custom meta query
        $qv['meta_query'] = [
            'custom' => [
                'key'   => 'test_date',
                'type'  => 'DATETIME'
            ]
        ];

        // Order by the 'custom' meta query
        $qv['orderby'] = 'custom';

    }
} );

where we've added the extra 'custom' key in the meta query, so that we can order by it as well. This would assume all users to have the test_date user meta key.

Hope you can adjust this accordingly.