#32592 closed defect (bug) (fixed)
Duplicate user results when using nested meta queries with inner OR relation
Reported by: | maxxsnake | Owned by: | boonebgorges |
---|---|---|---|
Milestone: | 4.3 | Priority: | normal |
Severity: | normal | Version: | 4.2.2 |
Component: | Users | Keywords: | has-patch |
Focuses: | Cc: |
Description
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.
Attachments (1)
Change History (6)
#2
@
9 years ago
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.
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?