Support

Account

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

Solved

Query: Multiple Checkbox Values (with LIKE) Crash

    • mvh89

    • April 12, 2019 at 10:02 pm

    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.

    • mvh89

    • April 16, 2019 at 5:32 pm

    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.

    • mvh89

    • April 16, 2019 at 9:58 pm

    Hi John,

    Got it! Will use the post_meta_data method:)
    I’ll post my edited code here when finished!

    Many thanks,

    Martijn

    • mvh89

    • April 17, 2019 at 10:47 pm

    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

    • mvh89

    • April 17, 2019 at 11:09 pm

    as I am not able to edit my previous post.
    Please note: The meta_key has to be different than your ACF field name.

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

You must be logged in to reply to this topic.

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 Cookie Policy. If you continue to use this site, you consent to our use of cookies.