Meta Query doesn't works as espected

Question

i have a little big problem with meta queries. I Designed a query to retrieve posts where any of these words exists in the custom field pickup_address. the field has this string in its meta value: 43 Longview Drive, Papamoa

EDIT

this is the meta query that Im using:

$args  = array(
'post_type'  => 'posts',
'meta_query' => array(
    'relation' => 'OR',
    array(
        'key'     => 'pickup_address', 
        'value'   => 'Papamoa', 
        'compare' => 'LIKE',
    ),
    array(
        'key'     => 'pickup_address', 
        'value'   => 'Bah�a',
        'compare' => 'LIKE',
    ),
    array(
        'key'     => 'pickup_address', 
        'value'   => 'de', 
        'compare' => 'LIKE',
    ),
    array(
        'key'     => 'pickup_address', 
        'value'   => 'Plenty',
        'compare' => 'LIKE', 
    ),
),

);

this is the code I generate from the meta queries:

SELECT SQL_CALC_FOUND_ROWS wpiw_posts.ID FROM wpiw_posts INNER JOIN
wpiw_postmeta ON ( wpiw_posts.ID = wpiw_postmeta.post_id ) WHERE 1=1
AND ( ( 
( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%Papamoa%' ) OR
( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%Bah�a%' ) OR 
( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%de%' ) OR 
( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%Plenty%' ) ) ) 
AND wpiw_posts.post_type = 'project' AND ((wpiw_posts.post_status = 'publish')) GROUP BY wpiw_posts.ID ORDER BY wpiw_posts.post_date DESC LIMIT 0, 10

I tried to replace the special character � but doesn't work either

Wordpress give me nothing on the Wp_Query($query_args) but if I run this in MySql it Works. Any help qould be appreciated

EDIT 2

I capture the query that Wordpress executes to MySql after converting the meta_query arguments:

SELECT SQL_CALC_FOUND_ROWS wpiw_posts.ID FROM wpiw_posts 
INNER JOIN wpiw_postmeta ON ( wpiw_posts.ID = wpiw_postmeta.post_id ) 
INNER JOIN wpiw_postmeta AS mt1 ON ( wpiw_posts.ID = mt1.post_id ) 
WHERE 1=1 AND 
( ( ( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%Papamoa%' ) 
OR ( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%BahÃa%' ) 
OR ( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%de%' ) 
OR ( wpiw_postmeta.meta_key = 'pickup_address' AND wpiw_postmeta.meta_value LIKE '%Plenty%' ) ) 
AND ( mt1.meta_key = 'deliver_address' AND mt1.meta_value LIKE '%Auckland%' ) ) 
AND wpiw_posts.post_type = 'project' AND ((wpiw_posts.post_status = 'publish')) GROUP BY wpiw_posts.ID ORDER BY wpiw_posts.post_date DESC LIMIT 0, 10

I notice the weird chacarters 'BahÃa', but when i take this query and run it on MySql, it works! retrieves 1 row because one post have a pickup_address field with the word Papamoa and have a deliver_address field with the work Auckland.

Answer 1

Mariolex,

Have you tried using meta_query? I wrote something quick and untested but it may do the trick:

$args  = array(
    'post_type'  => 'posts',
    'showposts'  => '-1',
    'meta_query' => array(
        array(
            'key'     => 'pickup_address', //meta_key
            'value'   => '43 Longview Drive, Papamoa', //meta_value
            'compare' => 'LIKE', //compare
        ),
    ),
);
$query = new WP_Query( $args );

print_r($query);

If that isn't working, you may be able to recreate a similar query to yours by breaking each part of the phrase into individual arrays of the meta_query options. I don't think you'll need to since the LIKE comparison should find what you are looking for.

Be sure to check out the Meta Query documentation on WordPress.org - it's a powerful tool that I use all the time for situations like this.

Sources:

• https://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters
• https://codex.wordpress.org/Class_Reference/WP_Meta_Query

EDIT

I misunderstood your query when I first read it. You can try this to see if you get a match for the value "43 Longview Drive, Papamoa".

$args  = array(
    'post_type'  => 'posts',
    'showposts'  => '-1',
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'     => 'pickup_address', //meta_key
            'value'   => 'Papamoa', //meta_value
            'compare' => 'LIKE', //compare
        ),
        array(
            'key'     => 'pickup_address', //meta_key
            'value'   => 'Bah�a', //meta_value
            'compare' => 'LIKE', //compare
        ),
        array(
            'key'     => 'pickup_address', //meta_key
            'value'   => 'de', //meta_value
            'compare' => 'LIKE', //compare
        ),
        array(
            'key'     => 'pickup_address', //meta_key
            'value'   => 'Plenty', //meta_value
            'compare' => 'LIKE', //compare
        ),
    ),
);
$query = new WP_Query( $args );

You may need to switch RELATION to "or" if you're looking for both.

Answer 2

After a long debug time, i figured out whats happened.

It seems that Wordpress doesn´t treat very well the latin punctuation, like accented vowels (á, é, í, ó, ú) and other characters.

using this method: Replacing accented characters php I change the vowels defore sending the data to MySql, and now, Wordpress show the posts