Meta query not showing result properly

Question

In wordpress posts I have custom meta fields (ship from, margin price). I want to filter post by these meta fields. Ship from fields contains multiple value (Example: A product can be ship from china and usa). Query is working fine while I am selecting china, but not working properly while selecting usa.

I am trying to figure out what is the issue with query.

The query is as follows:

$args = array(
   'post_type'  => 'post',
   'meta_query' => array(
        array(
             'key'    => 'ship-from',
             'value'  => 'usa',
             'compare'=> 'in'
        ),
        array(
             'key'       => 'profit-margin',
             'value'     => array(0,100),
             'meta_type' => 'numeric'
             'compare'   => 'BETWEEN'
        )
   )
);

$result = new WP_Query($args);

Here is the last query executed by WP_Query:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN 
wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN 
wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1  AND ( 
( wp_postmeta.meta_key = 'ship-from' AND wp_postmeta.meta_value IN 
('usa') ) AND ( mt1.meta_key = 'profit-margin' AND mt1.meta_value 
 BETWEEN '0' AND '50' ) ) AND wp_posts.post_type = 'post' AND 
((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY 
wp_posts.post_date DESC LIMIT 0, 12

Any input will be greatly appreciated.

Answer 1

You are missing relation argument for inner meta_query arrays as described in the Code Reference.

Also, when you use 'compare'=> 'IN', the 'ship-from' value should be the array.

$args = array(
   'post_type'  => 'post',
   'meta_query' => array(
        'relation' => 'AND', // note this!
        array(
             'key'    => 'ship-from',
             'value'  => 'usa',
             'compare'=> 'IN' 
        ),
        array(
             'key'       => 'profit-margin',
             'value'     => array(0,100),
             'meta_type' => 'numeric'
             'compare'   => 'BETWEEN'
        )
   )
);