[Closed] How to set View query filter for empty custom field

[Author]

Posts

[ABSTRACTUS]

Hi, I have a custom field "revisit date" (type: date) and already created a view with upcoming dates, and one with past (due) dates. In order to make sure nothing escapes from our radar, I would need a third view that displays all posts where this field is empty (or not set at all?)

What is the "magic query" that gives me the desired result? I have tried several combinations but always end up with "No items found"

Thank you for your support!

[Nigel]

I don't think you can enter such a condition through the UI, you would need to use the API to modify the query arguments and manually set the filter to check if there is no entry for the revisit date field at all or if there is any empty entry for it.

So create your View to check for no revisit dates, don't include any filters, and then use the wpv_filter_query API filter to set the custom field queries.

Here's an example of the kind of code you would use:

function tssupp_filter_query($view_args, $view_settings, $view_id)
{
    if (in_array($view_id, array( 123 ))) { // Edit View ID(s) 

        $view_args['meta_query'] = array(
            'relation' => 'AND',
            array(
                'key' => 'wpcf-active',
                'value' => '1',
                'compare' => '='
            ), 
            array(
                'relation' => 'OR',
                array(
                    'key' => 'wpcf-revisit-date',
                    'compare' => 'NOT_EXISTS'
                ),
                array(
                    'key' => 'wpcf-revisit-date',
                    'value' => '',
                    'compare' => '='
                )
            )
        ) 
    }

    return $view_args;
}
add_filter('wpv_filter_query', 'tssupp_filter_query', 101, 3);

You'll need to edit the ID of the View this should be applied to, and also check the keys of your custom fields are correct (wpcf-active, and wpcf-revisit-date). (It is easier to add all of the filters in the same block of code than to modify the query where you already have some filters added.)