WP Query – Multiple values in ACF Checkbox

• mtm

• May 28, 2021 at 9:09 pm

I’ve got quite a complex WP query on the go, and I need it to be a touch more granular on an ACF checkbox field. The logic is:

– Find ‘event’ post type
– Check ‘status’ field (acf checkbox)
– Check that the ‘Exclude Past’ OR the ‘Cancelled’ (or both) boxes are NOT checked
– Check for ‘end_date’ field and make sure it’s in the past
– If no ‘end_date’, check for start_date and make sure it’s in the past
– Order results by date used (‘end_date’ if there is a value, ‘start_date’ if not)

I have it working as I want except that I can’t figure out how to check for more than one value in the ‘status’ (checkbox) field. Here’s the code:

<?php 
 date_default_timezone_set('Europe/London'); 
 $date_1 = date('Ymd', strtotime("-999 months"));
 $date_2 = date('Ymd', strtotime("now"));
?>
<?php
 $past_args = array(
  'post_type' => 'event',
  'meta_query' => array(
   'relation' => 'AND',	
   'event_status_clause' => array(
    'key' => 'status',
    'value' => 'Exclude Past',
    'compare' => '!='
   ),
   'event_date_clause' => array(
    'relation'	=> 'OR',
    // check to see if end date has been set
    array(
     'key' => 'end_date',
     'compare' => 'BETWEEN',
     'type' => 'DATE',
     'value' => array($date_1, $date_2),
    ),
    // if no end date has been set use event/start date
    array(
     'key' => 'start_date',
     'compare' => 'BETWEEN',
     'type' => 'DATE',
     'value' => array($date_1, $date_2),
    )
   )
  ),
  'orderby' => 'event_date_clause',
  'order' => 'DESC',
  'nopaging' => false,
  'posts_per_page' => '4'
 );
?>

This works but it returns events with a status of ‘Cancelled’.

I tried using an array, as follows:

   'event_status_clause' => array(
    'key' => 'status',
    'value' => array('Exclude Past', 'Cancelled'),
    'compare' => '!='
   ),

But this gave me the following error warning:

Warning: trim() expects parameter 1 to be string, array given in /Users/matmartin/Local Sites/lmo/app/public/wp-includes/class-wp-meta-query.php on line 695

How do I get the query to check for value A or B in a checkbox?

Thanks!

• John Huebner

• May 28, 2021 at 10:51 pm

A check box in ACF stores a serialized array

'event_status_clause' => array(
    'key' => 'status',
    'value' => 'Exclude Past',
    'compare' => 'NOT LIKE'
   ),

I also you are using a type of “DATE” for acf date fields. This will not work because ACF does not store date fields in standard SQL notation. You must search these as “NUMERIC”

• mtm

• May 28, 2021 at 11:16 pm

Thanks John,

I can see how ‘NOT LIKE’ would work if there were only one option to consider. Here I need something like ‘not like exclude past OR not like cancelled’ (there are other checkbox values in this field which I do need to let through).

Possible values:
– Cancelled
– Exclude Past
– Online
– No Venue

Of course it’s also possible that none will be selected. The behaviour I am looking to create is that any post which has either the Cancelled or the Exclude Post (or both) checked is left out of the query, and that everything else gets through.

So ‘NOT LIKE’ still only excludes/includes based on a single possible checkbox value, correct? How do we do that for more than one of them?

That’s interesting about the date values. I’ve been using that part of this query for ages and it works fine for me (provided I set the return value of the date field correctly). I’ll switch to NUMERIC and see what happens!

Thanks.

• John Huebner

• May 29, 2021 at 2:01 am

Yes, you will have to create a nested meta query with an OR relation and either look for everything you want it to be LIKE or NOT LIKE

• mtm

• June 1, 2021 at 8:58 pm

Thanks John,

I get it – and here’s what I’ve written:

$past_args = array(
 'post_type' => 'event',
 'meta_query' => array(
 'relation' => 'AND',
 array(
  'relation' => 'OR',
  // check to see if end date has been set
  array(
   'key' => 'end_date',
   'compare' => 'BETWEEN',
   'type' => 'NUMERIC',
   'value' => array($date_1, $date_2),
  ),
  // if no end date has been set use event/start date
  array(
   'key' => 'start_date',
   'compare' => 'BETWEEN',
   'type' => 'NUMERIC',
   'value' => array($date_1, $date_2),
  )
 ),
 array(
  'relation' => 'OR',
  // check event is not cancelled
  array(
   'key' => 'status',
   'value' => '"Cancelled"',
   'compare' => 'NOT LIKE'
  ),
  // check event is not excluded from past events
  array(
   'key' => 'status',
   'value' => '"Exclude Past"',
   'compare' => 'NOT LIKE'
  )
 )
),
'orderby' => 'meta_value_num',
'order' => 'DESC',
'nopaging' => false,
'posts_per_page' => '4'
);

BUT – despite there clearly being an OR relation for the two queries on the status field, it is behaving like an AND relation. The event is only left out of the query if both boxes are checked.

Any idea why this would be? Might it have something to do with being inside an AND relation? The OR relation on the date clause is working fine. I’ve pulled this right back to individual nested queries to try and isolate the problem but it has really stumped me.

Thanks.

• mtm

• June 1, 2021 at 9:04 pm

I don’t seem to be able to post a reply here. My message is being labelled as a duplicate, although it’s not here.

• mtm

• June 1, 2021 at 9:09 pm

I’ve taken what you’ve suggested on board here John – thanks for the input. I understand and can see how it should definitely work. However, the OR relation is behaving like an AND relation, and I can’t work out why.

Here’s what I have now:

$past_args = array(
 'post_type' => 'event',
 'meta_query' => array(
 'relation' => 'AND',
 array(
  'relation' => 'OR',
  // check to see if end date has been set
  array(
   'key' => 'end_date',
   'compare' => 'BETWEEN',
   'type' => 'NUMERIC',
   'value' => array($date_1, $date_2),
  ),
  // if no end date has been set use event/start date
  array(
   'key' => 'start_date',
   'compare' => 'BETWEEN',
   'type' => 'NUMERIC',
   'value' => array($date_1, $date_2),
  )
 ),
 array(
  'relation' => 'OR',
  // check event is not cancelled
  array(
   'key' => 'status',
   'value' => '"Cancelled"',
   'compare' => 'NOT LIKE'
  ),
  // check event is not excluded from past events
  array(
   'key' => 'status',
   'value' => '"Exclude Past"',
   'compare' => 'NOT LIKE'
  )
 )
),
'orderby' => 'meta_value_num',
'order' => 'DESC',
'nopaging' => false,
'posts_per_page' => '9999'
);

The OR in the date clause is working fine. In the second set of queries (against the ACF checkbox) though, I am seeing all events with either checkbox value, and only missing those with BOTH values checked. This is what i would expect of an AND relation, no?

I removed the date clause entirely to try and isolate this but even then I can’t get the query to return events with only one of those two options checked.

Thanks.

• John Huebner

• June 2, 2021 at 9:20 pm

I think you need to do “LIKE” what you are looking for instead of “NOT LIKE”

Or you need to do a nested query for status an a relation of “AND” and “NOT LIKE”

if “Cancelled” is checked then NOT LIKE “Exclude Past” is true and visa-versa.

• mtm

• June 2, 2021 at 9:53 pm

I’m not sure how do check LIKE to exclude something – it would need to be LIKE everything it isn’t, but since multiple values are in play here (it’s a checkbox rather than a radio button) I think I can’t say LIKE a value and assume that means it is necessarily NOT LIKE one of the others.

That said, I have done this:

'meta_query' => array(
 'relation' => 'AND',
 array(
  'relation' => 'OR',
  // check to see if end date has been set
  array(
   'key' => 'end_date',
   'compare' => 'BETWEEN',
   'type' => 'NUMERIC',
   'value' => array($date_1, $date_2),
  ),
  // if no end date has been set use start date
  array(
   'key' => 'start_date',
   'compare' => 'BETWEEN',
   'type' => 'NUMERIC',
   'value' => array($date_1, $date_2),
  )
 ),
 array(
  'key' => 'status',
  'value' => '"Cancelled"',
  'compare' => 'NOT LIKE'
 ),
 array(
  'key' => 'status',
  'value' => '"Exclude Past"',
  'compare' => 'NOT LIKE'
 )
),

It seems to be working, although I don’t fully trust it. Now any event I designate as either Cancelled OR Exclude Past drops out of the past events list as I’d expect. Given that the clauses are part of an AND relation I’m not sure I understand why that’s happening?

Also (and as an aside), I’m noticing that when wrapping ORs inside ANDs like this things work pretty normally, but if I nest queries the other way around the page tends to hang instead of loading. Is that to be expected? Are there rules about which way round these things can be done?

Again, thanks so much John.

• John Huebner

• June 3, 2021 at 3:12 am

That will work too. You just need to nest part of the query to that you can use “OR” for parts and use “AND” for other parts.

The problem with doing a lot of like queries is speed. More than likely the nested like query is overtaxing the connection.

I’m going to be honest with you here. If i needed to query a checkbox field I would convert the ACF values into a standard WP meta field. Although if could be difficult if you already have a lot of content on the site.

• John Huebner

• June 3, 2021 at 3:14 am

Yes, the post is about repeaters but the same can be applied to fields that store serialized arrays, like checkbox fields.

• mtm

• June 3, 2021 at 7:42 pm

That sounds really interesting – great article, and thanks. I’m not advanced enough with PHP to be sure how you would tackle the part that cycles through the repeaters in your example for a checkbox field:

if (have_rows('colors', $post_id)) {
 while (have_rows('colors', $post_id)) {
  the_row();
  $color = get_sub_field('color');
  if (isset($saved_values[$color])) {
   continue;
  }
  add_post_meta($post_id, $meta_key, $color, false);
  $saved_values[$color] = $color;  
 }
}

Presumably we can’t do if(have_rows()) and while(have_rows()) if there are no rows?

I’m interested in doing this BEFORE the site I’m building gathers a lot of content, and learning something I can use again in future too. Would you mind showing me how you’d do this for a non-repeater?

Thanks so much.

• John Huebner

• June 3, 2021 at 9:55 pm

$values = get_field('status', $post_id);
foreach ($values as $value) {
  
}

other than that everything else is basically the same.

I am in the habit of always building this type of filter for any field that I may need to search by, even when there is not immediate plan to do so. They are quick to do, in fact I more or less have a standard filters that simply does it for all of a specific field type and appends “_wp” to whatever the field name is. standard filters will not work for repeaters though, but I do have a function that I feed in the repeater name and the sub field name I want to make it quicker.

• mtm

• June 4, 2021 at 12:43 am

Yes this does really seem like best practice, so I hope you’ll forgive me being dogged about understanding.

My understanding is clearly still flawed. from your article and your code above, I arrived at the following:

add_filter('acf/save_post', 'convert_event_status_to_standard_wp_meta', 20);
function convert_event_status_to_standard_wp_meta($post_id) {

 $meta_key = 'status_wp';
  delete_post_meta($post_id, $meta_key);
  $saved_values = array(); 
  $values = get_field('status', $post_id);
  foreach ($values as $value) {
  if (isset($saved_values[$value])) {
   continue;
  }
  add_post_meta($post_id, $meta_key, $value, false);
  $saved_values[$value] = $value;
 }
}

I placed this in functions.php (was that right?), and changed the query to include:

array(
 'meta_key' => 'status_wp',
 'value' => array('Cancelled', 'Exclude Past'),
 'compare' => '!='
)

This gave me the following error:

Warning: trim() expects parameter 1 to be string, array given in [url]/wp-includes/class-wp-meta-query.php on line 695

Obviously I’ve not implemented this correctly. I’m guessing either my query is wrong, or I’ve not adapted the repeater example from your article correctly in the foreach section of the code?

Thanks.