Advanced Custom Fields is a great plugin for handling custom fields and field groups in WordPress.
wp_postmeta table to store the data as key in one column and value in second. This is not perfect but guarantees that the data is perfectly aligned with "the WordPress way" of storing custom fields.
Whenever there is a need to fetch posts based on custom field value one can use
WP_Query parameters to get nice and easy results:
<?php $posts = get_posts([ 'meta_key' => 'color', 'meta_value' => 'red' ]); ?>
Situation complicates a bit when field can have multiple values (like relationship field, multi select) because then value is stored as serialized PHP array.
In this situation official ACF documentation points us towards using
LIKE comparison which is unfortunately not a perfect example:
Wrong (official) way:
<?php $posts = get_posts([ 'meta_query' => [ 'relation' => 'OR', [ 'key' => 'location', 'value' => 'Melbourne', 'compare' => 'LIKE', ], [ 'key' => 'location', 'value' => 'Sydney', 'compare' => 'LIKE', ], ] ]); ?>
Why is it wrong? Because it will match posts with location set to "Sydney" as well as "Sydney123".
While it looks like made up issue it becomes dangerous when using
[ key=> 'id', value=45] == would match id value of 45,456,145 etc.== which stops being funny.
<?php $id = 45; $posts = get_posts([ [...], 'meta_query' => [ [...], [ 'key' => 'id', 'value' => '"'. $id .'"', 'compare' => 'LIKE', ] ] ]); ?>
As mentioned before - multiple values are stored as serialized PHP array looking similar to
Adding quotes around a value ("45" instead 45) matches the way serialized array values are stored in DB and gives us proper results.
Please have in mind that this method works only if we are storing data in proper way (no white space or weird encoding). To make sure use any MYSQL administration tool like MYSQL Workbench or PHPMyAdmin and see how particular field value is being stored in DB.
I hope that this helps someone. In case of any questions feel free to use comments below.