Order posts according to user defined order for meta values?

Question

My proposed solution is already posted by @cybmeta (+1), but I add it anyway, since it deals with it in a general way 😉

In the same way the WP_Query parameters:

'post__in' => [1,2,3],
'orderby' => 'post__in'

give use the custom defined order:

ORDERBY FIELD( wp_posts.ID, 1, 2, 3 )

we can define our own meta ordering using:

'meta__in' => [ 'apple', 'orange', 'banana' ],
'orderby' => 'meta__in'

to get the following SQL part:

ORDERBY FIELD( wp_postmeta.meta_value, 'apple', 'orange', 'banana' )

First we define our own version of the wp_parse_id_list() helper function.

/**
* Note that this function is based on the core wp_parse_id_list() function.
* Clean up an array, comma- or space-separated list of string keys.
*
* @uses sanitize_key()
* @param array|string $list List of keys.
* @return array Sanitized array of keys.
*/
function wpse_parse_meta_list( $list )
{
    if ( ! is_array( $list ) )
        $list = preg_split('/[\s,]+/', $list);

    return array_unique( array_map( 'sanitize_key', $list ) );
}

Demo plugin

Then we construct the following demo plugin, to add a support for 'meta__in' ordering, in WP_Query:

<?php
/**
 * Plugin Name: Support for order by meta__in in WP_Query
 */

add_filter( 'posts_orderby', function ( $orderby, \WP_Query $q )
{
    if( $meta__in = $q->get( 'meta__in' ) )
    {
        if( is_array( $meta__in ) && ! empty( $meta__in ) )
        {
            global $wpdb;   

            // Transform the meta__in array into a comma separated strings of keys
            // Example [ 'apple', 'banana' ] --> "apple", "banana"
            $list=""" . join( '","', wpse_parse_meta_list( $meta__in ) ) . '"';           

            // Override the orderby part with custom ordering:
            $orderby = " FIELD( {$wpdb->postmeta}.meta_value, {$list} ) ";
        }
    }
    return $orderby;
}, 10, 2 );

/**
 * Note that this function is based on the core wp_parse_id_list() function.
 * Clean up an array, comma- or space-separated list of string keys.
 *
 * @uses sanitize_key()
 * @param array|string $list List of keys.
 * @return array Sanitized array of keys.
 */
function wpse_parse_meta_list( $list )
{
    if ( ! is_array( $list ) )
        $list = preg_split('/[\s,]+/', $list);

    return array_unique( array_map( 'sanitize_key', $list ) );
}

where we’ve added the helper function into the plugin.

Notes

We only use the 'meta__in' parameter for ordering and not for extra WHERE restrictions, like we have with the 'post__in' parameter. But that might be an interesting extension 😉

Also note that get_posts(), that’s just a WP_Query wrapper, has the following parameter set up by default:

'suppress_filters' => true

i.e. it doesn’t accept the posts_* filters by default.

So if you can’t use WP_Query or get_posts() with the suppress_filters => false, then you would need an alternative approach, e.g. suggested by @PieterGoosen.

Demo plugin

Notes

Related Posts: