Meta_query needs relation arg per key/value pair

[CoenJacobs]

Since #17011 introduced the relation param to meta_query args, it is possible to set it to 'AND' or 'OR' to define how the multiple key/value pairs should be related to each other. This can only be set for the entire meta_query array, so it will be applied to all key/value pairs.

Imagine the following meta_query array, where I want to get all posts where '_kind' is 'value_1' or 'value_2'. Either way, '_exclude' should ALWAYS be '-1'. This array will not work, since if any of the key/value pairs match, that post will be returned:

[meta_query] => Array
    (
        [0] => Array
            (
                [key] => _kind
                [value] => value_1
            )

        [1] => Array
            (
                [key] => _kind
                [value] => value_2
            )

        [2] => Array
            (
                [key] => _exclude
                [value] => -1
            )

        [relation] => OR
    )

So, I had to think of a way to set the relation per key/value pair. This would involve adding an extra array inside the meta_query array. Each group of key/value pairs can have its own 'relation' param, making the meta_query able to properly structure the query. Meta_query array will look something like this:

[meta_query] => Array
    (
        [0] => Array (

            [relation] => OR
            [0] => Array
                (
                    [key] => _kind
                    [value] => value_1
                )

            [1] => Array
                (
                    [key] => _kind
                    [value] => value_2
                )
            )

        [1] => Array (
            [relation] => AND
            [0] => Array
                (
                    [key] => _exclude
                    [value] => -1
                )
            )

        [relation] => AND
    )

Haven't figured out what would need to be changed to the WP_Query and WP_Meta_Query classes to support this change, but that shouldn't involve anything more than an extra loop (easy to port for backwards compat). Only thing I'm worried is what this will do to performance.

[scribu]

where '_kind' is 'value_1' or 'value_2'

You can use IN:

'meta_query' => array(
  array(
    'key' => '_kind',
    'value' => array( 'value_1', 'value_2' ),
    'compare' => 'IN'
  )
  ...
)

[CoenJacobs]

Replying to scribu:

You can use IN

Correct. Maybe this wasn't the very best example. What if the first array had to contain a specific value in two different keys, '_kind' and '_other_key' and the second array remains as in my example?

[scribu]

When you reach a certain level of complexity, you're better off using SQL directly:

function my_meta_query( $clauses, $wp_query ) {
  global $wpdb;

  if ( $wp_query->get( 'my_specific_meta_query' ) ) {
    $clauses['join'] .= "
      INNER JOIN $wpdb->postmeta m_kind ON (ID = m_kind.post_id AND meta_key = '_kind')
      INNER JOIN $wpdb->postmeta m_other ON (ID = m_other.post_id AND meta_key = '_other')
      INNER JOIN $wpdb->postmeta m_third ON (ID = m_third.post_id AND meta_key = '_third')
    ";

    $clauses['where'] .= " AND ((m_kind.meta_value = 'value_1' AND m_other.meta_value = 'value_1') OR (m_third.meta_value > m_kind.meta_value))
  }

  return $clauses;
}
add_filter( 'posts_clauses', 'my_meta_query' );

It's a little more work to set up the JOINs, but you end up with more readable code.

[CoenJacobs]

Check that, thanks for your (amazingly fast, btw) feedback Scribu!

[scribu]

I was just passing by. :P