Support

Account

Home Forums Add-ons Repeater Field Search 'between'

Solving

Search 'between'

  • Hi I’m trying to make a search for repeater fields.
    I have pages with custom fields number_#_start, number_#_end.
    User is providing a number and search should return all pages where provided number is between page number range.
    I’ve looked at this
    http://www.advancedcustomfields.com/resources/tutorials/querying-the-database-for-repeater-sub-field-values/
    But I couldn’t even manage to make it working for searching for exact number in one field… :/
    Any help would be appreciated

  • I know this is an old topic but for anyone looking to solve this problem I thought I post up my thoughts. The answer is not easy. First, this is impossible to do using WP_Query alone. You’re going to need to go directly to the database to perform the search using $wpdb. The documentation for this is here https://codex.wordpress.org/Class_Reference/wpdb

    Let’s take the example of matching repeater fields where the rows each contain a number and you want to find the posts where a value falls between these two numbers.

    The following is not exactly the code that needs to be used, it is a general outline of the steps that need to be taken with some code examples thrown in. You should take all the proper precautions, like using $wpdb->prepare(). I’m leaving out the finer details so that this does not become a book.

    Step 1) Do a query to get all of the rows where the value is >= the first number.

    
    $query = 'SELECT post_id, meta_key
              FROM wp_postmeta
              WHERE meta_key REGEX "repeater_name_[0-9]+_subfield_name"
                AND meta_value  <= ".$your_value."';
    $results = $wbdb->get_results($query);
    

    Step 2) Loop through the results of the first query to build the values we need to the where clauses in the next query.

    
    // $keys will hold a list of meta keys and their associated post_ids
    $keys = array();
    foreach ($results as $result) {
      // strip out the row
      $key = preg_replace('/^repeater_name_([0-9]+)_subfield_name$/', '\1', $result['meta_key'];
      if (!isset($keys[$key])) {
        // we haven't seen this one yet so set it up
        $keys[$key] = array();
      }
      // add the post id to the key array
      $keys[$key] = $result['post_id'];
    }
    

    Step 3) Now that we have the list we can start building the next query to get matching rows where the value is <= the end value

    
    // $where will hold a list of all the where clauses
    $where = array();
    foreach ($keys as $row => $posts) {
      $meta_key = 'repeater_name_'.$row.'_subfield_name';
      $where[] = '(meta_key = "'.$meta_key.'" 
                   AND meta_value >= ".$your_value."
                   AND post_id IN("'.implode('","', $posts).'"))';
    }
    $query = 'SELECT post_id 
              FROM wp_post_meta 
              WHERE '.implode(' OR ', $where);
    $results = $wbdb->get_results($query);
    

    Step 4) Loop though these results and get the list of posts to do a post__in query. See http://codex.wordpress.org/Class_Reference/WP_Query

    
    $posts = array();
    foreach ($results as $result) {
      $posts[] = $results['post_id'];
    }
    $args = array(
      'post_type' => 'your-post-type',
      'post_status' => 'publish',
      'posts_per_page' => -1,
      'post__in' => $posts;
    );
    $query = new WP_Query($args);
    

    Lot’s of work, yes it is. Repeaters are designed the way they are so that ACF can keep the rows together, I’m not the developer but I can tell you that given the way WP stores data there’s no other realistic way to do this other than use a separate table in the db to hold the data and that would make searching it more complicated, not less. They were also designed with the 99 percentile of users that will never want to search for posts in this way.

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

The topic ‘Search 'between'’ is closed to new replies.