Support

Account

Home Forums Front-end Issues Query: Multiple Checkbox Values (with LIKE) Crash

Solved

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

  • It may help to use taxonomies, but I can’t say for sure.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.