Sort posts by multiple meta values or meta keys.

WordPress doesn’t let you sort posts by multiple custom field values or keys if you’re using a meta_query. With a filter in your (child) theme’s functions.php you can order the posts with a new query parameter meta_query_orderby. The new parameter can be used in WP_Query and pre_get_posts. But as always there are some restrictions.

The filter

Put this code in your (child) theme’s functions.php or create a plugin with this code in it.

add_filter( 'query_vars', 'meta_query_orderby_query_vars' );
function meta_query_orderby_query_vars( $qvars ) {
	$qvars[] = 'meta_query_orderby';
	return $qvars;
}

add_filter( 'posts_orderby', 'meta_query_orderby_posts_orderby', 99, 2 );
function meta_query_orderby_posts_orderby( $orderby, $_this ) {
	global $wpdb;

	$meta_query_orderby = $_this->get( 'meta_query_orderby' );
	$sql = '';

	// Check if the query var 'meta_query_orderby' is used for this query.
	if ( !( !empty( $meta_query_orderby ) && is_array( $meta_query_orderby ) ) ) {
		return $orderby;
	}

	// Parse the query vars to retrieve the meta query sql.
	$meta_query = new WP_Meta_Query();
	$meta_query->parse_query_vars( $_this->query_vars );

	if ( !empty( $meta_query->queries ) ) {
		$clauses = $meta_query->get_sql( 'post', $wpdb->posts, 'ID', $_this );
	}

	// Check if the meta query sql where clause was retrieved.
	if ( !( isset( $clauses['where'] ) && $clauses['where'] ) ) {
		return $orderby;
	}

	// Get all sorting arrays custom field keys (keys are required in the sorting arrays).
	$keys = array_values( array_map( 'trim', wp_list_pluck( $meta_query_orderby, 'key' ) ) );

	// Match all meta tables or meta table aliases (wp_postmeta or mt1, mt2 etc..) with meta their meta keys.
	$pattern = "/($wpdb->postmeta|mt\d+)\.meta_key\s*=\s*'(" . implode( '|', $keys ) . ")'/";
	preg_match_all( $pattern  , $clauses['where'], $matches );

	if ( !( isset( $matches[2] ) && $matches[2] ) ) {
		return $orderby;
	}

	// Loop through all sorting arrays.
	foreach ( $meta_query_orderby as $meta ) {

		// Check if the required meta 'key' exists.
		if ( !( isset( $meta['key'] ) && $meta['key'] ) ) {
			continue;
		}

		// Get the numeric key from our matched keys.
		$matched_key = array_search( strtolower( (string) $meta['key'] ), array_map( 'strtolower', $matches[2] ) );

		// Check if matched key exists.
		if ( false === $matched_key ) {
			continue;
		}

		$meta_values = ( isset( $meta['value'] ) ) ? $meta['value'] : array();
		$meta_values = is_array( $meta_values ) ? $meta_values : array( (string) $meta_values );

		if ( isset( $matches[1][ $matched_key ] ) && $matches[1][ $matched_key ] ) {
			if ( !empty( $meta_values  ) ) {
				// Create sql to order by meta value.
				foreach ( (array) $meta_values as $value ) {
					$sql .= $matches[1][ $matched_key ] . ".meta_value = '" . esc_sql( $value ) ."' DESC, " ;
				}
			} else {
				// Create sql to order by meta key.
				$sql .= $matches[1][ $matched_key ] . ".meta_key = '" . esc_sql( $meta['key'] ) ."' DESC, " ;
			}
		}

	}

	return $sql . $orderby;
}

Examples

Example 1

In this example we query for posts with the custom field product_type and order the posts by this (meta value) order.

1. featured
2. premium
3. free

To query for posts with a custom field product_type we usually use something similar to this:

// Query arguments
$args = array(
	'meta_query' => array(
		array(
			'key'   => 'product_type', // Custom field key.
			'value' => array( 'free', 'featured', 'premium' ), // Order of values doesn't matter.
		),
		// Add more meta query arrays here if needed.
	),
);

// The query
$meta_query = new WP_Query( $args );

To sort the posts we add the new parameter meta_query_orderby and make sure the values in the value array are in the correct order. If you don’t use the value array (or leave it empty) you can order the posts by meta key only (see the next example).

// Query arguments
$args = array(
	'meta_query' => array(
		array(
			'key'   => 'product_type', // Custom field key.
			'value' => array( 'free', 'featured', 'premium' ), // Order of values doesn't matter.
		),
		// Add more custom field keys here if needed.
	),
	'meta_query_orderby' => array(

		// Custom field key sorting array.
		array(
			'key'   => 'product_type', // (required) Custom field key.
			'value' => array( 'featured', 'premium', 'free' ), // (optional) Order of values is important!
		),

		// Add more custom field key sorting arrays here if needed.
	),
);

// The query
$meta_query = new WP_Query( $args );

And voila, post will be ordered featured, premium, free

Example 2

In this example we query for posts with the custom fields movie or book And we want the posts with the book custom field to show first. And on top of that we want the the posts with the movie custom field ordered by these values.

1. action
2. drama

Here’s the entire query:

// Query arguments
$args = array(

	'meta_query' => array(
		'relation' => 'OR',

		array(
			'key'   => 'movie', // Custom field key.
			'value' => array( 'drama', 'action' ), // Order of values doesn't matter.
		),

		array(
			'key' => 'book', // Custom field key.
		),
	),

	'meta_query_orderby' => array(

		// Sorting array for custom field key
		array(
			'key'   => 'book', // (required) Custom field key.
		),

		// Sorting array for custom field key
		array(
			'key'   => 'movie', // (required) Custom field key.
			'value' => array( 'action', 'drama' ), // (optional) Order of values is important!
		),

	),
);

// The query
$meta_query = new WP_Query( $args );

Disclaimer

I’ve used this meta sorting in a few projects and have only tested it for the use cases there.

One restriction is that the order in the query has to be set to DESC for it to order the posts correctly.

Support

Please don’t post your question here if it doesn’t work. Post it in the WordPress forums and give it a tag: meta query orderby.