orderby several fields

[ejvindh]

Hi. Thanks for this awsome plugin.

I'm looking for ways to order the list by several fields. The usecase is that I want a list ordered by names. I have of course found the instruction about how to order by lastname:
[userlist meta_key="last_name" orderby="meta_value" order="ASC"]

But then sometimes my users have identical lastnames, and I want then to order by first_name.

I've tried several combinations, such as:
[userlist meta_key="last_name, first_name" orderby="meta_value" order="ASC"]
(result: nothing found)

[userlist meta_key="last_name" orderby="meta_value, first_name" order="ASC"]
(result: no difference from the suggested solution)

[userlist orderby="last_name, first_name" order="ASC"]
(result: No sorting at all)

[helgatheviking]

Currently I don't have support for passing multiple args via the shortcode. However, you can pass a custom query id, and then use a php snippet to build a more complex query as shown in the readme

[userlist query_id="my_custom_meta_query"]

It does look like WP can sort by X and then by Y by passing an array as the orderby param

[ejvindh]

Thanks for your feedback and suggestion. I will look into it, and see if I can get it working.

And thanks more generally for sharing your work with us 👍

[helgatheviking]

You could try this? No idea if it will work since the clause suggestions are AI generated and I can't test right now.

add_filter( 'sul_user_query_args', 'sul_custom_meta_query', 10, 2 );

function sul_custom_meta_query( $args, $query_id ) {
	// Checking the query ID allows us to only target a specific shortcode.
	if ( $query_id === 'my_custom_meta_query' ) {
		$args['meta_query'] = array(
			'relation' => 'AND',
				'last_name_clause'  => array(
					'key'     => 'last_name',
					'compare' => 'EXISTS',
				),
				'first_name_clause' => array(
					'key'     => 'first_name',
					'compare' => 'EXISTS',
				),
			),
			'orderby'    => array(
				'last_name_clause'  => 'ASC',
				'first_name_clause' => 'ASC',
			),
		);
	}
	return $args;
}

Could I get your opinion on how to support multiple meta keys... which seems more intuitive?

[userlist meta_key_1="size" meta_value_1="L" meta_key_2="color" meta_value_2="blue"]

Vs

[userlist meta_key="size|color" meta_value="L|blue"]

[ejvindh]

Thanks for your suggestions. I tried the AI-generated code, but it had some errors (too many closing parentheses). So, trying to understand what is going on in the code, I tried this:

add_filter( 'sul_user_query_args', 'sul_custom_meta_query', 10, 2 );

function sul_custom_meta_query( $args, $query_id ) {
	// Checking the query ID allows us to only target a specific shortcode.
	if ( $query_id === 'my_custom_meta_query' ) {
		$args['meta_query'] = array(
			'relation' => 'AND',
				'last_name_clause'  => array(
					'key'     => 'last_name',
					'compare' => 'EXISTS',
				),
				'first_name_clause' => array(
					'key'     => 'first_name',
					'compare' => 'EXISTS',
				),
			'orderby'    => array(
				'last_name_clause'  => 'ASC',
				'first_name_clause' => 'ASC',
			),
		);
	}
	return $args;
}

...however, it gives me no sorting of the list. (https://dmjx.ejvindh.net/eksempel-side/).

On my main page I have currently settled with a real "spagetti-hack", meaning that I have revised the ./plugins/simple-user-listing/simple-user-listing.php file, and added a usort function right after the generation of the $user-variable:

			$users = $sul_users->get_results();
                              usort( $users, function( $a, $b ) {
                                  $lastA = strtolower( get_user_meta( $a->ID, 'last_name', true ) );
                                  $lastB = strtolower( get_user_meta( $b->ID, 'last_name', true ) );
                      
                                  if ( $lastA === $lastB ) {
                                      $firstA = strtolower( get_user_meta( $a->ID, 'first_name', true ) );
                                      $firstB = strtolower( get_user_meta( $b->ID, 'first_name', true ) );
                                      return strcmp( $firstA, $firstB );
                                  }
                      
                                  return strcmp( $lastA, $lastB );
                              } );
                      

And yes, I realise that this solution will die next time you update the plugin :-]

-- however, a more stable solution will demand that I get to understand wp_query better, and how it interacts with your plugin. So, until then this was the best solution.

If you were to implement support for multiple keys, I think the second solution would be most intuitive. However, it would probably give me the expectation that it can handle "endless" amounts of keys. If you think about only supporting a limited amount of keys, probably the first solution would be best.

[ejvindh]

Ah, now I think I found a solution that works without the hack. Took a couple of rounds with Copilot:

I put the following code into functions.php:

add_action( 'pre_user_query', function( $query ) {
    if ( isset( $query->query_vars['orderby'] ) && $query->query_vars['orderby'] === 'custom' ) {
        global $wpdb;
        $query->query_orderby = "ORDER BY 
            (SELECT meta_value FROM $wpdb->usermeta WHERE user_id = $wpdb->users.ID AND meta_key = 'last_name') ASC,
            (SELECT meta_value FROM $wpdb->usermeta WHERE user_id = $wpdb->users.ID AND meta_key = 'first_name') ASC";
    }
});

And then added the following shortcode:
[userlist orderby="custom"]

[helgatheviking]

Nice! I was able to get this working as well:

add_filter( 'sul_user_query_args', 'sul_custom_meta_query', 10, 2 );

function sul_custom_meta_query( $args, $query_id ) {
	// Checking the query ID allows us to only target a specific shortcode.
	if ( $query_id === 'my_custom_meta_query' ) {
		$args['meta_query'] = array(
			'relation' => 'AND',
			'last_name_clause' => array(
				'key' => 'last_name',
				'compare' => 'EXISTS'
			),
			'first_name_clause' => array(
				'key' => 'first_name',
				'compare' => 'EXISTS'
			)
			);
		$args['orderby'] = array(
			'last_name_clause' => 'ASC',
			'first_name_clause' => 'ASC'
		);
	}
	return $args;
}

The orderby arg needed to be outside of the meta_query.

As for multiple keys:

However, it would probably give me the expectation that it can handle "endless" amounts of keys.

Technically i suppose you could support a lot of keys via a foreach loop, but I'm not sure I would advise it though. :) Meta queries aren't the most performant but I do my best to cache the results so the query doesn't have to run every time.

[ejvindh]

Yes, your last version works here as well.

... but I do my best to cache the results so the query doesn't have to run every time.

Ah, that explains why it always take several reloads of the page when I experiment with alternating solutions 😁 -- But of course, in ordinary use it makes perfectly sense ☺️

And yes, I understand that too many ordering-parameters might slow things down. But my feeling would be that most users of this plugin do have some understanding of such issues, so perhaps it can be handled with a simple warning in the readme-file.

Or perhaps, and now I'm just suggesting: Would it not be faster simply to do the sorting without wp_query, using a usort-function instead?

Or: Would it be possible to handle the sorting through a php-file in the simple-user-listing-folder. Just like the way you handle the layout of the user-lists?

[helgatheviking]

Ah, that explains why it always take several reloads of the page when I experiment with alternating solutions

It shouldn't? The cache key is built from a hash of the query args so when they change the cache should refresh (famous last words)

If you have steps to reliably reproduce then perhaps you can open a new issue?

[helgatheviking]

Some initial support for multiple meta keys in https://github.com/helgatheviking/Simple-User-Listing/releases/tag/2.1.0-beta.1 but sorting by multiple keys isn't there yet.

[helgatheviking]

Reopening to leave it for myself as a followup to #43

[ejvindh]

Great, support for multiple metakeys might very well come in handy!

And yes, I'll open a new issue about the cache-thing.