[Resolved] Run a meta_query against checkboxes post

[Author]

Posts

[nicholasG-2]

Hi there,

I'm trying to use 'wpv_filter_query' to alter my query based on some $_POST data. I have a checkboxes field, `wpcf-racial-identity`, and I've added a custom value, which when selected, triggers altering the query. That all works fine (thanks to your help in https://toolset.com/forums/topic/default_label-is-not-working-with-the-wpv-control-postmeta-shortcode/#post-2723129), but I'm having trouble using meta_query with a checkboxes field. When I select the 'All POC' button, I want to query all posts where the 'wpcf-racial-identity' exists (exclude all posts that have not interacted with this filter) and which do NOT have the 'White or Caucasian' attribute checked.

Here's my current function:
`
function rhdwp_maestra_filter_all_poc_checkbox( $query_args, $view_settings, $view_id ) {
if ( 416 != $view_id ) {
return $query_args;
}

// Check if the search is for the "All Racial Identity" checkbox
if ( isset( $_POST['search']['dps_general'] ) ) {
foreach ( $_POST['search']['dps_general'] as $value ) {
if ( 'wpv-wpcf-racial-identity[]' == $value['name'] && 'all-poc' == $value['value'] ) {
// Create a new meta_query array
$query_args['meta_query'][] = [
'relation' => 'AND',
[
'key' => 'wpcf-racial-identity',
'compare' => 'EXISTS',
],
[
'key' => 'wpcf-racial-identity',
'value' => 'White or Caucasian',
'compare' => 'NOT LIKE',
],
];

error_log( print_r( $query_args, true ) );

// No need to continue the loop.
return $query_args;
}
}
}

return $query_args;
}
add_filter( 'wpv_filter_query', 'rhdwp_maestra_filter_all_poc_checkbox', 99, 3 );
`

I believe the query is formatted properly. When I do a print_r() on the $query_args, here's what I get:

`
Array
(
[paged] => 1
[post_type] => Array
(
[0] => maestra_profile
)

[order] => ASC
[suppress_filters] =>
[ignore_sticky_posts] => 1
[posts_per_page] => 24
[wpv_original_limit] => -1
[wpv_original_offset] => 0
[wpv_original_posts_per_page] => 24
[meta_query] => Array
(
[0] => Array
(
[key] => wpcf-pending
[value] => 1
[type] => NUMERIC
[compare] => <
)

[relation] => AND
[2] => Array
(
[relation] => AND
[0] => Array
(
[key] => wpcf-racial-identity
[compare] => EXISTS
)

[1] => Array
(
[key] => wpcf-racial-identity
[value] => White or Caucasian
[compare] => NOT LIKE
)

)

)

[post_status] => Array
(
[0] => publish
[1] => private
)

[post__in] => Array
(
[0] => 0
)

)
`

I've verified that there are valid entries in the db. I couldn't find much info on running a meta_query against a checkboxes field, but the best lead I had was using LIKE and NOT LIKE. Any assistance would be greatly appreciated!!

Cheers,
Nick

[Nigel]

Hi Nick

I'm not surprised you are struggling to manipulate the query if it relates to a checkboxes field. Deep in the mists of time checkboxes fields were implemented, and the why's are lost to us now, even though we can determine the how's by looking at the code, puzzling at the choices.

To be fair, I think the challenges arise from having an options field where all of the option values may be the same; when setting up a checkboxes field, while giving the options different labels ("Red", "Blue", "Green") users often give them the same values ("1", "1", and "1").

So if you look in wp_postmeta at how the checkboxes field values are stored (as serialised arrays), the option values are stored, but also generated unique option keys, given that the values might not be the same.

So, on my local test site, a post with a checkboxes field "checkers" that has colours for options is stored like this:

a:2:{s:64:"wpcf-fields-checkboxes-option-39f8503ff967e1c6e89b40e1ebdf90f4-1";a:1:{i:0;s:3:"red";}s:64:"wpcf-fields-checkboxes-option-49bb77867db3c504ff31fb02e40e0600-1";a:1:{i:0;s:6:"purple";}}

Unserialised, that translates to

Array
(
    [wpcf-fields-checkboxes-option-39f8503ff967e1c6e89b40e1ebdf90f4-1] => Array
        (
            [0] => red
        )

    [wpcf-fields-checkboxes-option-49bb77867db3c504ff31fb02e40e0600-1] => Array
        (
            [0] => purple
        )

)

Anticipating your task, I set up my colours checkboxes field such that the stored values are unique (colour 'slugs', rather than '1').

That way the raw postmeta value includes the name of what I'm targeting, making it simpler to modify a View query to specify checkbox options to include or exclude.

(If you set up your checkboxes field options to just store '1' as the saved value this becomes much more difficult, as there is no public API to retrieve those scary-looking option keys, and I strongly encourage you to modify the settings for your checkboxes field so that the saved value for the options use unique values. If you have to do that, you will have to re-save posts that use the checkboxes fields for the stored values to be updated to match the new settings.)

To illustrate how you can then modify the View query using the wpv_filter_query hook, I adopted a simple scenario where I wanted to include any post with a checkbox option of "red" and exclude any post that has a checkbox option of "brown". (I'm not responding to the values set in the filters by users, just imposing those conditions by way of illustration.)

function tssupp_filter_query( $view_args, $view_settings, $view_id )  {
      
    if ( in_array( $view_id, array( 24 ) ) ) {
     
        $view_args['meta_query'] = [
            'relation'  => 'AND',
            [
                'relation'  => 'AND',
                [
                    'key'   => 'wpcf-checkers',
                    'compare'   => 'LIKE',
                    'value' => 'red',
                    'type'  => 'CHAR'
                ],
                [
                    'key'   => 'wpcf-checkers',
                    'compare'   => 'NOT LIKE',
                    'value' => 'brown',
                    'type'  => 'CHAR'
                ],
            ]
        ];
            
    }    
    return $view_args;
  }
add_filter( 'wpv_filter_query', 'tssupp_filter_query', 101, 3 );

This works because the query is just looking at the contents of the meta_value column in wp_postmeta as raw strings, and the way they are saved (as serialised arrays, which are strings) means we can search the text for matches for the option values we specify.

I think you should have enough there to modify your code?