0

I know in MySQL exists a query like this:

SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);

Is there a way to accomplish the same in WP_Query? I want to order especifically the meta_value like this: 0, 2, 1, 3, 4

Here are my args:

$args_com_subset = array(
              'posts_per_page' => -1,
              'post_type' => 'company',
              'meta_key' => 'company_tiers',
              'orderby' => 'meta_value',
              'order' => 'ASC',           
              'meta_query' => array(
                    'relation'      => 'OR',
                    array(
                        'key' => 'company_type',
                        'value'    => 'full_methodology_n_subset_of_indicators',
                        'compare'    => '='
                    ),
                    array(
                        'key' => 'company_type',
                        'value'    => 'subset_of_indicators',
                        'compare'    => '='
                    ) 
                ),
                'tax_query' => array(       
                    'relation'      => 'AND',         
                    array(
                        'taxonomy' => 'company_year',
                        'field' => 'slug',
                        'terms' => '2020'
                    )
                )
            );
1

First remove the order section from your args, also add nested meta_queries like the following example:

$args_com_subset = array(
    'posts_per_page' => -1,
    'post_type' => 'company',  
    'meta_key' => 'company_tiers', 
    'meta_query' => array(
        'relation'      => 'AND',
        array(
            'key'     => 'company_tiers',
            'value'   => array( 1,2,3,4 ),
            'compare' => 'IN',
        ),
        array(
            'relation'      => 'OR',
            array(
                'key' => 'company_type',
                'value'    => 'full_methodology_n_subset_of_indicators',
                'compare'    => '='
            ),
            array(
                'key' => 'company_type',
                'value'    => 'subset_of_indicators',
                'compare'    => '='
            ) 
        )
    ),
    'tax_query' => array(       
        'relation'      => 'AND',         
        array(
            'taxonomy' => 'company_year',
            'field' => 'slug',
            'terms' => '2020'
        )
    )
);

Then you have to add a filter to alter the query's sort part, remember to remove this filter after query execution to garantee that it won't affect any other queries:

add_filter( 'posts_orderby', 'meta_key' ); 
$posts = new WP_Query($args);
remove_filter( 'posts_orderby', 'custom_sql_order' ); 

function custom_sql_order($orderby) { 
    global $wpdb; 
    $orderby = "FIELDS('wp_postmeta.meta_value', 3,2,1,4)";
    
    return $orderby;
} 
| improve this answer | |
  • Nice, I was just writing an answer using 'posts_orderby' filter too. Interested to see if this works – mozboz Jul 2 at 23:41
  • I got a mysql error in the logs "Syntax error near '(mt1.meta_value, 0,2,1,3,4)' at line 1" – ricardorios Jul 3 at 0:04
  • Updated sort filter to reflect this. – Skatox Jul 4 at 19:32

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.