Home › Forums › Front-end Issues › Query: Multiple Checkbox Values (with LIKE) Crash
Hi fellow ACF users,
I have a checkbox called: “functions” with ~10 values.
I want users to filter by value.
This i my WordPress Meta Query:
Array
(
[numberposts] => 999
[post_type] => sporthorloge
[posts_per_page] => 999
[post_status] => publish
[orderby] => meta_value
[order] => DESC
[meta_key] => score
[meta_query] => Array
(
[0] => Array
(
[relation] => AND
[0] => Array
(
[key] => functies
[value] => "slaapmonitor"
[compare] => LIKE
)
[1] => Array
(
[key] => functies
[value] => "muziekspeler"
[compare] => LIKE
)
[2] => Array
(
[key] => functies
[value] => "stappenteller"
[compare] => LIKE
)
[3] => Array
(
[key] => functies
[value] => "snelheid"
[compare] => LIKE
)
[4] => Array
(
[key] => functies
[value] => "traptreden"
[compare] => LIKE
)
[5] => Array
(
[key] => functies
[value] => "ingebouwde_hartslagmeter"
[compare] => LIKE
)
)
)
[type] => NUMERIC
)
As you can see there are multiple %LIKE% queries used. This is every heavy on my MYSQL server. It crashed!
Is there a way I can optimize this?
Or is the only solution creating Yes/No Checkboxes for each value?
I made it multipleselect because I dont want to have 10 extra fields for 40+ posts. That would be 400 extra fields in the database.
Any thoughts or suggestions would be appreciated!
Many thanks!
Martijn
There isn’t any way to optimize this query, the only hope you have is to optimize the data…. that is store it in a way that does not require all of the LIKE matches on un-indexed columns of the database. This can be done the same way that I explain for repeaters here https://acfextras.com/dont-query-repeaters/
Beyond this, pre-indexing search results, which is too complex to go into here, this is how most search plugins speed up searching.
Hi John,
Thanks for your answer and pointing me to your blog article. You explain it in a very simple way. You are definitely right.
One questions though.
Do you know if I create a “normal” true false checkbox the values will be saved as post_meta_data?
Otherwise I will tweak your “repeater code” for my multiselect.
What would you advise?
Thanks,
Martijn
True/False fields would all be saved as 1/0. You would decrease the number of “LIKE” meta queries and may help a small amount. However the meta_value column in the DB is not indexed. The fewer rows you need to look at the faster the query will perform. Using a true/false for each option would mean looking at more rows and mean more joins.
Hi John,
Got it! Will use the post_meta_data method:)
I’ll post my edited code here when finished!
Many thanks,
Martijn
In case somebody faces the same problems as I did.
For getting the selection field and post its values as meta data:
// Credits: John Heubner
// create a function that will convert this repeater during the acf/save_post action
// priority of 20 to run after ACF is done saving the new values
add_filter('acf/save_post', 'convert_color_to_standard_wp_meta', 20);
function convert_color_to_standard_wp_meta($post_id) {
// pick a new meta_key to hold the values of the color field
// I generally name this field by suffixing _wp to the field name
// as this makes it easy for me to remember this field name
// also note, that this is not an ACF field and will not
// appear when editing posts, it is just a db field that we
// will use for searching
$meta_key = 'watch_functions';
// the next step is to delete any values already stored
// so that we can update it with new values
// and we don't need to worry about removing a value
// when it's deleted from the ACF repeater
delete_post_meta($post_id, $meta_key);
// create an array to hold values that are already added
// this way we won't add the same meta value more than once
// because having the same value to search and filter by
// would be pointless
$saved_values = array();
$functions = get_field('functies');
if(!empty($functions))
{
foreach($functions as $function)
{
// see if this value has already been saved
// note that I am using isset rather than in_array
// the reason for this is that isset is faster than in_array
if (isset($saved_values[$function])) {
// no need to save this one we already have it
continue;
}
// not already save, so add it using add_post_meta()
// note that we are using false for the 4th parameter
// this means that this meta key is not unique
// and can have more then one value
add_post_meta($post_id, $meta_key, $function, false);
// add it to the values we've already saved
$saved_values[$function] = $function;
} // end while have rows
} // end if have rows
} // end function
And quering the database.
$posts = get_posts(array(
'numberposts' => -1,
'post_type' => 'watch',
'posts_per_page' => 100,
'meta_key' => 'score',
'orderby' => 'meta_value',
'order' => 'DESC',
'meta_query' => array(
array(
'relation' => 'AND',
array(
'key' => 'watch_functions',
'compare' => '=',
'compare' => 'calorieenteller',
)
)
),
));
Cheers,
Martijn
as I am not able to edit my previous post.
Please note: The meta_key has to be different than your ACF field name.
Hi john,
I am still having troubles executing the queries, since apparently quering meta keys by 5+ meta_keys at once takes up a lot of resources.
@hube2 – My question: would it be better to use taxonomies for these type of filtering?
Thanks in advance,
Martijn
The topic ‘Query: Multiple Checkbox Values (with LIKE) Crash’ is closed to new replies.
Welcome to the Advanced Custom Fields community forum.
Browse through ideas, snippets of code, questions and answers between fellow ACF users
Helping others is a great way to earn karma, gain badges and help ACF development!
We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Privacy Policy. If you continue to use this site, you consent to our use of cookies.