A point on Date fields in WP_Query

• Nox

• January 24, 2019 at 2:09 am

Hi,

I’m currently developping a website for a car seller company.
I need to render a list of vehicles which have not been sold yet or sold less than three day ago.

To check this condition, I lean on a field named “date_sale” which indicates the exact date when the vehicle has been sold. The logic is pretty simple : if the field isn’t completed, the vehicle has not been sold yet, if it is, there’s a date, so I can check if this one is smaller than three day ago.

Here are my query’s args :

    $date_limit = date('Ymd', strtotime("-3 day")) . ' 00:00:00';
    $args = array(
        'post_type'      => 'vehicle',
        'post_status'    => 'publish',
        'posts_per_page' => 10,
        'meta_key'       => 'date_sale',
        'orderby'        => 'meta_value',
        'pagination'     => true,
        'meta_query'     => array(
            'relation' => 'OR',
            array(
                'key' => 'date_sale',
                'compare' => '>=',
                'value' => $date_limit,
            ),
            array(
                'key' => 'date_sale',
                'compare' => '=',
                'value' => ''
            )
        )
    );

Here I got two issues :

1. I’ve previously checked on threads where people asked for the same operation, and generaly they recommande to use the couple “compare” => “NOT EXISTS” in order to get a post archive with post having no value in the compared field. In my case, it didn’t work at all. I must compare the value as it is an empty one (so with = and value => ”) in order to get my posts without a date of sale. If I use ‘compare’ => “NOT EXISTS”, I got all published vehciles (with and without a date or sale), as if the argument actually check if the key well exists for this post-type, even if it is empty. I wonder why isn’t this working with me…

2.

                'key' => 'date_sale',
                'compare' => '>=',
                'value' => $date_limit,

This part is totally ignored by the query. I got all vehicles which have no dates thanks to the second test, but I got no vehicle which has been sold less than three days ago. So, I searched on a lot of threads how to compare two dates using ACF, and I always found differing notices : some people recommand you to use the “Ymd” format, others “Y-m-d”, others said that it’s important to add the hours like ’00:00:00′ at the end of the date, some tell you to add a ‘type’ => ‘DATE’ argument in the array, other say that you finally need other input format which is more exotic than belived, but unfortunatelly none of those propositions worked. I didn’t manage to compare thoses two dates.

Can someone enlighten me on this subject please ?

Thank you in advance,
Nox

• feyfey

• January 24, 2019 at 9:33 am

Hi Nox,

For your first question : the ‘NOT EXISTS’ operator means the metadata isn’t set or is set to null. So to understand why this operator doesn’t work in your case you can try this in the single_post.php (just to testing it):

if( ! in_array( 'date_sale', get_post_custom_keys( $post_id ) ) ) {
Echo ‘The metadata isn’t set, the NOT EXISTS operator must works’; 
} else {
$my_sale_date_meta = get_post_meta($post_id, ‘date_sale’);
If(is_null($my_sale_date_meta)){
Echo ‘The metadata is null, the NOT EXISTS operator must works’;
} else {
Echo ‘The metadata is set and not null, the NOT EXISTS operator can\’t work’;
}
}

If you echo “… the NOT EXISTS operator must works” that’s means another part of your code make it wrong, else, it’s perfeclty normal it’s not working in your case because even if your field is empty the metadata is set and not null.

For your second issue, you have to process some testing too : Are you sure the metadata date_sale can be compared to you $date_limit ? Use the get_post_meta() to print your date_sale, but it’s possible that your date_sale return a string which can’t be compared to a DateTime. In that case you must convert your date_limit to a comparable format

Hope it’s help.

• Nox

• January 24, 2019 at 7:23 pm

Hi,

I received an email that tell that the user Feyfey answered my thread, however for a weird reason, I can’t see the post here.

For my first question, I presume it was ok to said that ACF finally asigned an empty string (and not a null value) in the meta key. Maybe it happens when entering and removing a date manually in the field, then save the post. However I haven’t been able to make work the “NOT EXISTS” comparasion yet.

For my second one, here’s finally a group of arguments which worked for me :

   $date_limit = date('Y-m-d', strtotime("-3 days"));
    $args = array(
        'post_type'      => 'vehicle',
        'meta_key'       => 'date_sell',
        'meta_query'     => array(
            'relation' => 'OR',
            array(
                'key' => 'date_sell',
                'compare' => '>=',
                'value' => $date_limit,
                'type' => 'DATE'
            ),
            array(
                'key' => 'date_sell',
                'compare' => '=',
                'value' => ''
            )
        )
    );

So with a Date type and ‘Y-m-d’ format.

Hope it’ll help.

Nox

• feyfey

• January 24, 2019 at 7:45 pm

Hi Nox,

Yes I sent you an answer, not visible here … Weird. I don’t know if you get me answer by email even if it isn’t visible here ? What matters is you get your solution =)

For the first issue, yes you’re right, we can assume “ACF finally asigned an empty string (and not a null value) in the meta key” because as I try to tell in my “ghost answer” the ‘NOT EXISTS’ operator means the metadata isn’t set or is set to null. When you have this kind of issue the best way is to test your metadata this way (use it in single_post.php for exemple) :

if( ! in_array( 'date_sale', get_post_custom_keys( $post_id ) ) ) {
    Echo ‘The metadata isn’t set, the NOT EXISTS operator must works’; 
} else {
    $my_sale_date_meta = get_post_meta($post_id, ‘date_sale’);
    If(is_null($my_sale_date_meta)){
        Echo ‘The metadata is null, the NOT EXISTS operator must works’;
    } else {
        Echo ‘The metadata is set and not null, the NOT EXISTS operator can\’t work’;
    }
}

For your second issue, I assume your first arguments doesn’t works because your ‘date_sell‘ format isn’t comparable to the $date_limit format. If you have another issue like this, the best way to find an answer is to print your arguments using native WP or PHP function (not ACF one which may “traduce” the metadata depending on field settings).

Have a good coding day !