Get Posts from Custom Meta Query

Question

I want to query posts using the get_posts() function and am having trouble getting the query to work properly. The $country, $us_state, $ca_state, $mx_state values will be pulled from a Gravity Forms entry.

Here is the query:

$post_type = 'reps';
$state_meta_key = 'rep_state';
$country_meta_key = 'rep_country';

$country = 'Mexico';
$us_state = '';
$ca_state = '';
$mx_state = '';

$reps = get_posts( 
    array(
        'posts_per_page' => -1,
        'post_type' => $post_type,
        'meta_query' => array(

            array(
                'key' => $country_meta_key,
                'value' => $country,
                'compare' => 'LIKE'
            ),
            array(
                'key' => $state_meta_key,
                'value' => $us_state,
                'compare' => 'LIKE'
            ),
            array(
                'key' => $state_meta_key,
                'value' => $ca_state,
                'compare' => 'LIKE'
            ),
            array(
                'key' => $state_meta_key,
                'value' => $mx_state,
                'compare' => 'LIKE'
            )

        )
    )
);

With this query, I will get a positive result because there is a post with meta data for $country_meta_key AND $state_meta_key, but if I change the $country value to Ireland, I will NOT get a result.

This is because the post that has a value of Ireland for the $country_meta_key does NOT have a value for $state_meta_key.

All posts with a $state_meta_key value will automatically have a $country_meta_key value because the Country value must exist before a State value can be chosen. BUT not all posts will have a $state_meta_key value (a post can have a Country value and keep the State value blank).

Here is an example of 2 posts:

Post 1
rep_country = Mexico
rep_state = Mexico - Estado de

Post 2
rep_country = Ireland
rep_state =

Update 1

The following code works to give a result when the state value is null, but (obviously) this code will not allow for querying any post by State value:

array(
        'key' => $country_meta_key,
        'value' => $country,
        'compare' => 'LIKE'
    ),
    array(
        'key' => $state_meta_key,
        'compare' => 'NOT EXISTS'
    )

Whenever I use the OR relation, that opens up the query to all posts within this post type (it's not specific enough).

How can I query the posts by Country value (where the post will NOT have State value) OR by State value (where the post will always have a Country value)?

Answer 1

Add 'relation' => 'OR' to your Meta Query, I think it will be something like this...

'meta_query' =>  array(
   'relation' => 'AND', // Optional, defaults to "AND"
   array(
        'key' => $country_meta_key,
        'value' => $country,
        'compare' => 'LIKE'
   ),
array(
    'relation' => 'OR',
     array(
            'key' => $state_meta_key,
            'value' => $us_state,
            'compare' => 'LIKE'
        ),
        array(
            'key' => $state_meta_key,
            'value' => $ca_state,
            'compare' => 'LIKE'
        ),
        array(
            'key' => $state_meta_key,
            'value' => $mx_state,
            'compare' => 'LIKE'
        )
)
); 

https://codex.wordpress.org/Class_Reference/WP_Meta_Query

Answer 2

I figured out the solution. Here is the code:

'meta_query' => array(

    'relation' => 'OR',
    array(
        array(
            'key' => $country_meta_key,
            'value' => $country,
            'compare' => 'LIKE'
        ),
        array(
            'key' => $state_meta_key,
            'compare' => 'NOT EXISTS'
        )
    ),
    array(
        array(
            'key' => $state_meta_key,
            'value' => $us_state,
            'compare' => 'LIKE'
        ),
        array(
            'key' => $state_meta_key,
            'value' => $ca_state,
            'compare' => 'LIKE'
        ),
        array(
            'key' => $state_meta_key,
            'value' => $mx_state,
            'compare' => 'LIKE'
        ),
        array(
            'key' => $country_meta_key,
            'value' => $country,
            'compare' => 'LIKE'
        )
    )

)

This will query posts that have a Country value where NO State value EXISTS, OR query posts that have a State value AND a Country value.