I am trying to fetch from the WP database posts with two meta_key values, but the result is 0
posts.
Print out of the arguments:
Array (
[taxonomy] => cardstax
[term] => christmas
[post_type] => cards
[paged] => 1
[caller_get_posts] => 1
[meta_query] => Array (
[relation] => AND
[0] => Array (
[key] => card_format[value] => Little card
)
[1] => Array (
[key] => price_lvl
[value] => Array (
[0] => 0.06
[1] => 0.97
)
[type] => NUMERIC
[compare] => BETWEEN
)
)
)
The Meta query itself
'taxonomy' => $term->taxonomy,
'term' => $term->slug,
'post_type' => 'cards',
'paged'=> $paged,
'caller_get_posts'=> 1,
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'card_format',
'value' => 'Little card'
),
array(
'key' => 'price_lvl',
'value' => array( "0.06","0.97" ),
'type' => 'NUMERIC',
'compare' => 'BETWEEN'
)
),
The SQL syntax:
SELECT SQL_CALC_FOUND_ROWS plt_posts.ID
FROM plt_posts
INNER JOIN plt_term_relationships ON ( plt_posts.ID = plt_term_relationships.object_id )
INNER JOIN plt_postmeta ON ( plt_posts.ID = plt_postmeta.post_id )
INNER JOIN plt_postmeta AS mt1 ON ( plt_posts.ID = mt1.post_id )
JOIN plt_icl_translations t ON plt_posts.ID = t.element_id
AND t.element_type = 'post_cards'
JOIN plt_icl_languages l ON t.language_code = l.code
AND l.active =1
WHERE 1 =1
AND ( plt_term_relationships.term_taxonomy_id IN ( 27 ) )
AND plt_posts.post_type = 'cards'
AND (
plt_posts.post_status = 'publish'
OR plt_posts.post_status = 'private'
)
AND (
(
plt_postmeta.meta_key = 'card_format'
AND CAST( plt_postmeta.meta_value AS CHAR ) = 'Little card'
)
AND (
mt1.meta_key = 'price_lvl'
AND CAST( mt1.meta_value AS SIGNED )
BETWEEN '0.06'
AND '0.97'
)
)
AND t.language_code = 'lv'
GROUP BY plt_posts.ID
ORDER BY plt_posts.post_date DESC
LIMIT 0 , 12
Without that part
AND (
mt1.meta_key = 'price_lvl'
AND CAST( mt1.meta_value AS SIGNED )
BETWEEN '0.06'
AND '0.97'
)
The query works fine and I get a list of post IDs.
Remove the quotes around the values for your second meta query. You’re comparing numbers to chars, which won’t work.
Changed DECIMAL and NUMERIC to CHAR and it worked out.