Get posts using multiple values from ACF checkbox as meta query wordpress

Question

$args = array(
        'posts_per_page' => -1,
        'post_type'  => 'products',
        'meta_query' => array(
          array(
            'key'     => 'colors',
            'value'   => serialize(array('red', 'green')), 
            'compare' => 'IN',
          ),
        ),
      );

<?php
      $the_query = new WP_Query($args);
      if ($the_query->have_posts()) {
        while ($the_query->have_posts()) : $the_query->the_post(); ?>
          <?php echo get_the_title(); ?><br>
          <?php $counter++; ?>
      <?php endwhile;
        wp_reset_postdata();
      } else {
        /** no posts found **/
      } ?>

I have a query like the above, it works fine and it fetches posts with the exact value of checkbox with red and green. however, when a posts have checkbox value of red and green, but the query only wants green, the post with checkbox value red and green is not fetched. is there anyway to achieve that?

Answer 1

IN operator for ACF query is not working; as meta values are stored in serialized form in DB.

So change your meta query as below.

$meta_query = array(
'relation' => 'OR',
[
    'key'      => 'colors',
    'value'    => '"red"',
    'compare'  => 'LIKE',
],
[
    'key'      => 'colors',
    'value'    => '"green"',
    'compare'  => 'LIKE',
],

);