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.

  • possible duplicate of WP_Query - Order results by meta value – Mayeenul Islam Sep 30 '15 at 7:39
  • 1
    @MayeenulIslam I think you have missed the question ;-) – Pieter Goosen Sep 30 '15 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! – Rutwick Gangurde Sep 30 '15 at 7:41
  • 2
    You will be, IMHO, better of sorting the results with usort. It is going to be very complicated, but doable – Pieter Goosen Sep 30 '15 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 '15 at 7:53
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.

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

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.

  • Let me try this! I can apply the filter before the get_posts call and remove it immediately after. – Rutwick Gangurde Sep 30 '15 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 '15 at 9:17
  • Yes! I am looking at it! – Rutwick Gangurde Sep 30 '15 at 9:20
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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