WP REST API: Order Users by meta value (acf)

I have an issue where I need to return users based on a custom field. I have created these custom fields with an excellent plugin called “Advanced Custom Field” (ACF).

ACF plugin allows you to show custom fields in WordPress REST API as well but you do not have any out of the box way to sort users by these custom fields.

when I dig into details on how ACF generate these fields, these are usermeta created against a user and stored in usermeta database.

Based on the above fact I created a custom REST API endpoint that returns users by a specified sort order and based on a custom usermeta (or custom field)

In this custom API endpoint I just write a MySQL query to get results I need such as

SELECT *, 
CONVERT(SUBSTRING_INDEX(meta_value,'-',-1),UNSIGNED INTEGER) as userbalance 
FROM `usermeta`
WHERE
meta_key = 'userbalance'
ORDER BY userbalance DESC
LIMIT 10 OFFSET 0

Now I tested the above query in PhpMyadmin and make sure it works fine.

The next step is to create a custom WordPress REST API endpoint.

Below is the code I have written on the function.php page in the WordPress theme folder

function getgameusers_func($request) {
	$sort = $request->get_param('sort');
    $page = $request->get_param('page');
	global $wpdb;
	
	if($page>0)
	{
		$page = ($page-1)*10;
	}
    $query = $wpdb->get_results( "SELECT *, CONVERT(SUBSTRING_INDEX(meta_value,'-',-1),UNSIGNED INTEGER) as userbalance FROM `usermeta` WHERE meta_key = 'userbalance' ORDER BY userbalance DESC LIMIT 10 OFFSET {$page}" );
	
	return $query;
	
}

add_action('rest_api_init', function () {
     register_rest_route( '/wp/v2', '/userbalance/', array(
       'methods' => 'GET',
       'callback' => 'getgameusers_func'
     ));
});

The above code will create a custom endpoint /wp/v2/userbalance that returns users with a custom sort order.