Duplicate user results when using nested meta queries with inner OR relation

[maxxsnake]

When using nested meta queries as recently introduced (​https://make.wordpress.org/core/2014/10/20/update-on-query-improvements-in-4-1/), the query returns duplicate users when multiple matches are found on the inner "OR" relation queries, as "DISTINCT" is omitted from the generated SQL select.

<?php  

$args = array(
	'count_total' => true,
	'meta_query' => array(
		'relation' => 'AND',
		array(
			'first_name_clause' => array(
				'key' => 'first_name',
				'compare' => 'EXISTS',
			),
			'last_name_clause' => array(
				'key' => 'last_name',
				'compare' => 'EXISTS',
			),
		),
		array(
			'relation' => 'OR', // Inner OR, fails to add DISTINCT to query_fields
			array(
				'compare' => 'LIKE',
				'key' => 'first_name',
				'value' => 'jo',
			),
			array(
				'compare' => 'LIKE',
				'key' => 'last_name',
				'value' => 'jo',
			),
			array(
				'compare' => 'LIKE',
				'key' => 'name_custom_meta_key',
				'value' => 'jo',
			),
		),
	),
	'number' => 2,
	'orderby' => 'last_name_clause first_name_clause',
	'role' => 'roster_agent',
);
$query = new WP_User_Query( $args );

?>

In user.php, starting on line 677, I noticed that DISTINCT is only added when the relation is set to OR:

<?php

if ( 'OR' == $this->meta_query->relation ) {
	$this->query_fields = 'DISTINCT ' . $this->query_fields;
}

?>

The relation appears to be set based on the root-level meta_query key only, as is set during instantiation of the WP_Meta_Query class in meta.php starting on line 979.

[boonebgorges]

maxxsnake - Thanks very much for the detailed report. I've confirmed the bug as well as your diagnosis.

32592.diff​ is one way to go about fixing this. It provides a utility method has_or_relation() for checking to see whether a single OR appears in any of the meta clauses. It does the trick, though it's a bit on the ugly side. maxxsnake, do you have thoughts about the approach?

[maxxsnake]

Hi Boone

Looks like it should do the trick, thanks for the quick response. I was using a slightly messier approach to resolve the matter for my current project, although without modifying core directly I believe it was as best as I could have implemented. Will be awaiting it's removal once the patch is available :-)

My temporary hack was to recursively search the meta query for an OR relation:

<?php

class Project_Class {
	public static function select_distinct_users( $query ) {
		if ( ! empty( $query->meta_query->queries ) && self::_has_nested_relation( 'OR', $query->meta_query->queries ) ) {
			if ( ! empty( $query->query_fields ) && strpos( $query->query_fields, 'DISTINCT' ) !== 0 ) {
				$query->query_fields = 'DISTINCT ' . $query->query_fields;
			}
		}
	}

	private static function _has_nested_relation( $relation, $queries ) {
		if ( is_array( $queries ) ) {
			foreach ( $queries as $key => $value ) {
				if ( is_array( $value ) ) {
					$value = self::_has_nested_relation( $relation, $value );
					if ( $value !== false ) {
						return true;
					}
				}
				elseif ( $key === 'relation' && $value === $relation ) {
					return true;
				}
			}
			return false;
		}
	}
}
add_filter( 'pre_user_query', array( 'Project_Class', 'select_distinct_users' ) ); // In actual project added filter only where necessary

?>

Naturally, since recursion is already occurring inside WP_Meta_Query->sanitize_query(), it makes more sense in core to go ahead and set the property to trigger DISTINCT at that time as you have already done.

[boonebgorges]

Thanks, maxxsnake - looks like it's essentially the same solution.

[boonebgorges]

In 32713:

Avoid returning duplicate matches when using a meta query in WP_User_Query.

A meta_query containing an OR relation can result in the same record matching
multiple clauses, leading to duplicate results. The previous prevention against
duplicates [18178] #17582 became unreliable in 4.1 when WP_Meta_Query
introduced support for nested clauses. The current changeset adds a new method
WP_Meta_Query::has_or_relation() for checking whether an OR relation
appears anywhere in the query, and uses the new method in WP_User_Query to
enforce distinct results as necessary.

Props maxxsnake.
Fixes #32592.

[knutsp]

#32603 was marked as a duplicate.