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.