Especific order by Custom Field Values in WP_Query

Question

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'
                    )
                )
            );

Answer 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;
}