WP-Query and Searching Inside Arrays

Question

I have a plugin that stories a grid of integers in meta fields like so:

'pricing_info' => 
  array (
    0 => '1 | 1 | 1',
    1 => '1.5 | 1.5 | 1.5',
    2 => '22 | 22 | 22',
  ),

So its basically using the | as a divider. Not great design, but alas here we are.

The three integers within each array element represent 3 different variables.

How can I define my meta_query to search for each 3 of those variables with different user inputs.

Example: assume the three variables are $_POST['var1'], $_POST['var2'], $_POST['var3'].

We need to take those three variables and search with the each element of 'pricing_info' array using meta_query (where each variable is divided by |).

Is this even possible within meta-query?

Thank you so much for any guidance.

Answer 1

For array and object meta values, WordPress serializes them to string representations using serialize(). Thus, your meta value looks like this in the database:

a:3:{i:0;s:9:"1 | 1 | 1";i:1;s:15:"1.5 | 1.5 | 1.5";i:2;s:12:"22 | 22 | 22";}

There is no built-in way to query within these serialized strings, but since these are strings with a consistent structure, one could consider using regular expressions in a meta query:

/**
 * Gets a regular expression for matching pricing info.
 *
 * @param {int}        $index Which pricing info value to lookup.
 * @param {string|int} $value The match against.
 * @return {string} A regular expression string to match for the pricing info
 *                  index and value.
 */
function get_regexp( $index, $value ) {
  return 'i:' . $index . ';s:\d+:"(|[^"]+\| )' . $value . '("| \|)';
}

$query = new WP_Query(
  array(
    // …
    'meta_query' => array(
      array(
        'key'     => 'pricing_info',
        'compare' => 'REGEXP',
        'value'   => get_regexp( 0, $_POST['var1'] ),
      ),
      array(
        'key'     => 'pricing_info',
        'compare' => 'REGEXP',
        'value'   => get_regexp( 1, $_POST['var2'] ),
      ),
      array(
        'key'     => 'pricing_info',
        'compare' => 'REGEXP',
        'value'   => get_regexp( 2, $_POST['var3'] ),
      ),
    ),
  ),
);

For example, where $index = 0 and $value = 1, the regular expression could be

i:0;s:\d+"(|[^"]+\| )1("| \|)

Which means:

• i:0;s: matches the start of a serialized numerical array entry literally. The 0 is the array key.
• \d+ matches one or more digits. This matches the s:9, s:15, etc. of the prefix before the array value.
• " matches literal " character for the start of the array value.
• (|[^"]+\| ) is a grouping with two distinct possibilities, separated by | (not to be confused with your syntax that also uses |):
  • Left side, (|, is asserting to match nothing. This is the case one when the value could be the first one in the list, i.e. s:9:"1.
  • Right side, |[^"]+\| ):
    • [^"]+ matches one or more characters as possible that are not the " character. This ensures we match only within the given array value string.
    • \| matches the pipe symbol | and then a single space literally. This ensures we are anchoring the regular expression match from the start of a number value, and not in the middle. For example, this partly ensures when 1 is passed in, we match 1 but not 515.
  • 1 matches the value we want to match literally.
  • ("| \|) is a grouping with two distinct possibilities, separated by |. These are needed to ensure we don't partially match a number. For example, this partly ensures when 1 is passed in, we match 1 but not 515
    • Left side, ", matches " literally to assert a match at the end of an array value.
    • Right side, \|, matches a single space and then the pipe symbol | literally.