2

I have set up some CPTs and storing a meta key/val for each of them. When I get those posts, I need to order them by the meta value, but, the order of the meta values will be defined in an array by me. The default ordering for meta values is either alphabetical or numerical, which I do not want.

I'll explain it in an example:

Post 1 =>
Meta key: Fruit
Meta value: Apple

Post 2 =>
Meta key: Fruit
Meta value: Melon

Post 3 =>
Meta key: Fruit
Meta value: Guava

Post 4 =>
Meta key: Fruit
Meta value: Banana

I get the order from an external source, and I want to get the posts in that order. Example: I get an order in an array: ['Apple', 'Banana', 'Guava', 'Melon']. When I fetch the CPTs, I need to get them in this order. (Ignore the alphabetical ordering, I am just trying to give a general example).

How do I achieve it using a meta_query or any other solution?

If there is an existing thread, please redirect me to it.

11
  • possible duplicate of WP_Query - Order results by meta value Sep 30, 2015 at 7:39
  • 1
    @MayeenulIslam I think you have missed the question ;-) Sep 30, 2015 at 7:41
  • I know that approach, but it orders the posts by the alphabetical order or numerical order of meta values assigned to them. I want to be able to define the order. @PieterGoosen Right, he did! Sep 30, 2015 at 7:41
  • 2
    You will be, IMHO, better of sorting the results with usort. It is going to be very complicated, but doable Sep 30, 2015 at 7:48
  • 2
    Defining a custom order for MySQL queries needs the use of ORDER BY FIELD(), which you can introduce using posts_orderby filter. But I'm not sure if it is better than select the posts and then order the results using PHP functions as @PieterGoosen suggests. You could study the two options.
    – cybmeta
    Sep 30, 2015 at 7:53

4 Answers 4

2

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.

5
  • I'm the usort() guy here, you are the funny extra parameters (which I prefer above mine) guy here, hahahaha. +1 Sep 30, 2015 at 8:49
  • hehehe, the more variations in solutions, the merrier ;-) @PieterGoosen
    – birgire
    Sep 30, 2015 at 8:58
  • It makes the stack more useful. Sep 30, 2015 at 9:01
  • Very nice and complete answer. +1
    – cybmeta
    Sep 30, 2015 at 9:11
  • Looks pretty awesome! Trying it. Sep 30, 2015 at 9:20
2

If the value of the meta field is a single value (not serialized), this filter could work:

add_filter( 'posts_orderby', 'custom_posts_orderby' );
function custom_posts_orderby( $orderby_statement ) {

    $custom_order = array( 'Apple', 'Banana', 'Guava', 'Melon' );

    $custom_order = "'" . implode ( "', '", $custom_order ) . "'";

    $orderby_statement = "FIELD( meta_value, " . $custom_order . " )";

    return $orderby_statement;

}

Note that this will affect to all queries, so you need to define the conditions to apply filter or not.

You can see more examples to build the ORDER BY FIELD() statemnet in this post.

4
  • Let me try this! I can apply the filter before the get_posts call and remove it immediately after. Sep 30, 2015 at 9:12
  • See @birgire's answer, it follows the same approach but it is really better. It implements the filter in a way that allow you to specify the custom order in meta__in agument of WP_Query or get_posts, then this argument is checked in the filter, so you don't need to worry about any conditional to apply the filter or not. Just set the meta__in argument and you are ready to go.
    – cybmeta
    Sep 30, 2015 at 9:17
  • Yes! I am looking at it! Sep 30, 2015 at 9:20
  • Brilliant, thanks. Have edited the implode as it wasn't putting quotes around the strings which is needed inside the SQL
    – mozboz
    Sep 13, 2022 at 20:02
0

As I stated, IMHO, with your best option for what you need is going to be usort() to sort the returned array of posts

Here is an untested theory

$sortorder = ['Apple', 'Banana', 'Guava', 'Melon'];
$args = [
    'meta_query' => [
        [
            'key'   => 'Fruit',
            'value' => $sortorder
        ]
    ],
];
$q = new WP_Query( $args );

@usort( $q->posts, function( $a, $b ) use ( $sortorder  ) {
    // Get the meta values from the two posts to be compared
    $array_a = get_post_meta( $a->ID, 'Fruit', true );
    $array_b = get_post_meta( $b->ID, 'Fruit', true );

    // Reverse our $sortorder, key/value becomes value/key
    $flipped_order = array_flip( $sortorder );

    // Let start our sorting
    // If our meta values are the same order by date
    if ( $flipped_order[$array_a] != $flipped_order[$array_b] ) {
        return $flipped_order[$array_a] - $flipped_order[$array_b] // Swop around to reverse ordering
    } else {
        return $a->post_date < $b->post_date; // Change to > if you need oldest posts first
    }
});

// Run your loop normally

EDIT

I have not brought in any safety to my code. You would want to make sure that $sortorder is a valid array, then you need to sanitize and validate the values before you use them to avoid malicious code.

You can do this for the main query aswell, the only thing is then that you would want to wrap the usort() function in the the_posts filter and then use $posts in place of $q->posts

0

Building on the answer of @cybmeta here's another snippet of code that shows example of how you might sensibly decide when to this filter should activate. This assumes that you've specified an order by the meta_key on the query object somewhere, e.g. by hooking parse_query.

function custom_orderby_orders($orderby, $query) {

  if ($query->query['post_type'] == 'some_post_type' &&
      $query->query_vars['meta_key'] == 'meta_key_you_want_to_order') {
    
    $neworder = ['foo', 'bar', 'baz'];

    $ordersql = "'" . implode("', '", $neworder) . "'";

    return "FIELD(meta_value, " . $ordersql . ")";

  } else { 

    return $orderby;

  }
}

add_filter('posts_orderby', 'custom_orderby_orders', 10,2);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.