0

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.

  • When you say that the ship from field can have multiple values, how are these stored? – Jacob Peattie Oct 4 '18 at 10:24
  • @JacobPeattie they are stored as string. like china,usa – Deepak Singh Basnal Oct 4 '18 at 10:28
  • You’re not really going to be able to efficiently query them if you store them like that. You should store them as separate rows. – Jacob Peattie Oct 4 '18 at 10:54
0

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'
        )
   )
);
| improve this answer | |
  • I read somewhere that default relation is AND for meta query – Deepak Singh Basnal Oct 4 '18 at 11:26
  • Yes, you are right. Anyway, you have to check the 'ship-from' value and/or comparison operator. – Max Yudin Oct 4 '18 at 11:41
  • I have one question. I am passing array in meta value for profit-margin meta key, but in query array values automatically convert into string. Is there any way to change these values to numeric ? – Deepak Singh Basnal Oct 4 '18 at 11:53
  • It's becomes serialized string, and WordPress takes care about serializing/unserializing it. If you use WordPress' built-in functions to work with post meta like update_post_meta(), get_post_meta(), WP_Queries and related stuff you don't have to worry about value type and structure. – Max Yudin Oct 4 '18 at 12:28

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.