How do I filter for the presence of a date field (timestamp)? The value could be NULL or a date. I can pick up entries where there is a value, but cannot figure out how to identify entries with NULL
Hi, if you want to set up a Query Filter to find Users with a NULL value for a date usermeta custom field created in Types, then you could set up a Query Filter as shown in the screenshot here where the field value is a string equal to an empty constant. This User View would then return all Users who have the date custom field key saved in the usermeta table with a NULL or empty value....However, it would be unusual to find this situation, where a NULL or empty value exists for a usermeta date field, in most Toolset sites. When a User is created in wp-admin and no value is provided for the date field, the system does not enter a NULL or empty value in the usermeta table of the database. Instead, the field key is simply not added as a row in the table. This is technically different from a NULL or empty value: the usermeta field does not exist for this User. Similarly, when editing a User in Forms or in wp-admin, if the custom date field has a value set but then it is unset, then the custom field key is usually removed from the database instead of inserting a NULL or empty value.
I want to make sure you have considered this nuance, since the Query Filter I mentioned earlier is only for the case where a NULL or empty value exists in the database. Do you have some custom code in your site that is setting NULL or empty values for this custom date usermeta field?
Hi Christian
Trust me, these fields exist with a NULL value. They are not Types Custom fields. They form part of Learndash Groups implementation. I assume it is done that way for a reason. If a user has access to a course but hasn't completed the course, the value is NULL. Once completed, it contains the timestamp
Okay thanks, I was running some tests and it seems that the empty string constant query filter I suggested before does not help display results with a NULL value, only those having an empty value. It turns out that custom code will be required here to manipulate the query using our wpv_filter_user_query API. We have documentation for that API available here: https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_user_query
I can look for some example code that tests for NULL values...I think the EXISTS / NOT EXISTS meta query options may be best but I'll need to test that.
It turns out it's not so easy to filter a query to show Users that contain a null value in a usermeta field. You can't accomplish it by modifying the meta query terms alone, it requires some modification to the SQL query itself. Here's an example showing how it can be done with a postmeta query: https://wordpress.stackexchange.com/questions/269416/search-custom-post-with-meta-value-null/269444
However, they use a posts_where filter here to modify the SQL query. There isn't a corresponding users_where filter as far as I can tell, so I don't have a cut-and-paste solution here. I guess one workaround would be to get the Users where a date value is set (I believe you mentioned before that you were able to accomplish this), then query all Users and filter out those users where a date value is set (an exclusion filter).
Hi Christian,
I have managed to do my selection using different criteria (different usermeta).
The 'null' test seems to work in the conditions. Thanks for your help. Hopefully I won't have to come back to this.... but there is lots of useful information in here with regards selection methods. Thanks for your help