There’s an old riddle I heard while growing up that I used to hate.
You have two coins that add up to 30 cents, but one isn’t a nickel. What are the two coins?
The answer, of course, is a quarter and a nickel. Like many riddles, it comes down to an assumption we make when parsing the question. In this case, it’s absolutely true that one of the coins isn’t a nickel — but the other one is.
This riddle felt particularly salient to some work I was doing in WordPress this week, around the logic of MySQL queries and the WP_Meta_Query functionality.
As sometimes an example can explain a bit more than theoretical, consider the following:
Post ID: 50, has a title of "Fifty", and two separate rows in the postmeta table, both under the 'color' key -- 'red', and 'blue'.
Post ID: 51, has a title of "Fiftyone", and one row in the postmeta table, under the 'color' key -- 'red'How would you query for all posts that have color of red, but do not have color of blue? My first attempt was something along the lines of this:
get_posts(
array(
'meta_query' => array(
array(
'key' => 'color',
'compare' => '=',
'value' => 'red',
),
array(
'key' => 'color',
'compare' => '!=',
'value' => 'blue',
),
),
)
)
However, to my initial surprise, this returned both ID 50 and ID 51! So I pulled up the query to see why that could be, and it turned out the generated SQL looked something like:
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE ( wp_postmeta.meta_key = 'color' AND wp_postmeta.meta_value = 'red' )
AND ( mt1.meta_key = 'color' AND mt1.meta_value != 'blue' )
GROUP BY wp_posts.ID
(slightly simplified)
Well, you would ask, how does that return post ID 50? Well, because both of the joins to the meta table wind up finding the same meta! We say not to return a result with a meta key of blue, but SQL interprets that as find me any entry with a meta key that is not blue — and as post ID 50 also has a meta key that is red (which is not blue) we’re in business!
Anyway, finding the absence of something in the where clause is tricky — as the where clause specifies what you want to select. I’d looked and I’m pretty sure just using what’s already there in the WP_Meta_Query class it’s not possible to properly structure this, I’d done some work on it a decade ago, implementing the EXISTS and NOT EXISTS comparators.
So we could get the result we wanted, if we rewrote the above MySQL to:
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id AND mt1.meta_key = 'color' AND mt1.meta_value = 'blue' )
WHERE ( wp_postmeta.meta_key = 'color' AND wp_postmeta.meta_value = 'red' )
AND ( mt1.post_id IS NULL )
GROUP BY wp_posts.ID
The rough idea is that it will attempt to do a left join instead of an inner join, which could not turn up any results from the joined table — so we explicitly want to query for rows where a column that would have to be there — post_id — being NULL to ensure that there was nothing for it to join to. Thereby ensuring it NO HAZ any blue values.
So here’s what I wound up writing. It’s still rough, but functional thus far in my testing (so long as the NO HAZ isn’t the first parameter in the meta_query — as written the regex relies on the aliasing of the table name to parse it out). There’s probably some cleanup that it needs, however …
/**
* An extra filter to let our custom `NO HAZ` meta_compare work!
*/
function no_haz_meta_compare( $sql, $queries, $type, $primary_table, $primary_id_column, $context ) {
global $wpdb;
foreach ( $queries as $key => $query ) {
if ( ! empty( $query['compare'] ) && 'NO HAZ' === $query['compare'] ) {
// If we've got a NO HAZ query, pull out the table alias it's using for the join.
$matches = array();
$search = sprintf(
'#\( (\w+)\.meta_key = \'%1$s\' AND (\w+)\.meta_value = \'%2$s\' \)#',
preg_quote( $query['key'], '#' ),
preg_quote( $query['value'], '#' )
);
if ( preg_match( $search, $sql['where'], $matches ) ) {
$table_alias = esc_sql( $matches[1] );
// And then rewrite the JOIN bit to do a LEFT join instead of inner, so it can check for null (absence)
$sql['join'] = str_replace(
array(
sprintf(
"INNER JOIN {$wpdb->postmeta} AS {$table_alias} ON ( {$wpdb->posts}.ID = {$table_alias}.post_id )",
$matches[1]
),
sprintf(
"LEFT JOIN {$wpdb->postmeta} AS {$table_alias} ON ( {$wpdb->posts}.ID = {$table_alias}.post_id )",
$matches[1]
),
),
$wpdb->prepare(
"LEFT JOIN {$wpdb->postmeta} AS {$table_alias} ON ( {$wpdb->posts}.ID = {$table_alias}.post_id AND {$table_alias}.meta_key = %s AND {$table_alias}.meta_value = %s )",
$query['key'],
$query['value']
),
$sql['join']
);
// And now yeet the original WHERE bit and swap it to testing for `post_id` in the postmeta table being null -- or confirming that none were found.
$sql['where'] = str_replace(
$matches[0],
"( {$table_alias}.post_id IS NULL )",
$sql['where']
);
}
}
}
return $sql;
}
add_filter( 'get_meta_sql', 'no_haz_meta_compare', 10, 6 );
which would permit the following to return only post id 51:
get_posts(
array(
'meta_query' => array(
array(
'key' => 'color',
'compare' => '=',
'value' => 'red',
),
array(
'key' => 'color',
'compare' => 'NO HAZ',
'value' => 'blue',
),
),
)
)
The code would probably be a lot cleaner if it was in WP_Meta_Query proper, instead of regex parsing it all out — or if there was a filter in `WP_Meta_Query::get_sql_for_clause` to allow overriding — but hopefully this serves as a useful proof of concept for someone else hitting a similar issue down the road.
George Stephanis 