Group Meta Query - allow more than one meta_key

[imath]

"Bonsoir" !

Since last ticket, i've been exploring this new powerful feature for groups loop : the groups meta query. First i'd like to say it's a really great feature that would help me so much for a current project if it could play with more than one meta_key at a time.

Let's consider this groups loop argument :

$group_args = array( 
	'type' => 'active',
	'meta_query' => array(
		'relation' => 'AND',
		array(
			'key' => '_grooks_featured',
			'value' => 1,
			'type' => 'numeric',
			'compare' => '='
		),
		array(
			'key' => '_grooks_profile',
			'value' => 's:4:"arts"',
			'compare' => 'LIKE'
		),
		array(
			'key' => '_grooks_group',
			'value' => 1,
			'type' => 'numeric',
			'compare' => '='
		)
	)
);

if i run the loop, the result is a sql error such as "unknown column mt1.group_id" or "unknown column mt2.group_id, even if the data are set in bp_groups_groupmeta.

So i dived into BP_Groups_Group::get() and mostly BP_Groups_Group::get_meta_query_sql(). In this last function, i think (unless the goal is to only allow one meta_key query) that it should use preg_match_all on $meta_sql['join'] to get all the table alias, it should get all the where clauses. The relation "OR" also needs to use DISTINCT, and i had to modify the $total_groups_sql to avoid another sql error after my edits.

Sorry for the last part if i explained badly :(

So i suggest the diff attached to this ticket as it seems to work on my config.

[boonebgorges]

Good find, imath, and good patch.

I'm a little bit worried adding that the DISTINCT clause will have some edge-case effects on existing installations that are using certain kinds of filters on the sql query, and so I'm a little wary of putting this into the 1.8.1 release. On the other hand, the meta_query param is pretty broken without this fix. So, I'm going to go ahead and add it, and we'll see if the sky falls.

[boonebgorges]

In 7322:

Fixes groups meta_query for multiple clauses

The 1.8 implementation of meta_query for BP_Groups_Group::get() did not
properly handle multiple clauses, because of certain necessary
transformations of the SQL syntax to match the peculiar syntax in the
groups component. This changeset fixes the problem by improving the
logic used to parse the SQL returned from the WP_Meta_Query object.

A related included fix is that a DISTINCT keyword has been added to the
main SELECT clause of the groups total sql statement. This ensures that
multi-clause meta_query queries will not return duplicate values.

Also adds an automated test for the situation described above.

Fixes #5119

[boonebgorges]

In 7323:

Fixes groups meta_query for multiple clauses

The 1.8 implementation of meta_query for BP_Groups_Group::get() did not
properly handle multiple clauses, because of certain necessary
transformations of the SQL syntax to match the peculiar syntax in the
groups component. This changeset fixes the problem by improving the
logic used to parse the SQL returned from the WP_Meta_Query object.

A related included fix is that a DISTINCT keyword has been added to the
main SELECT clause of the groups total sql statement. This ensures that
multi-clause meta_query queries will not return duplicate values.

Also adds an automated test for the situation described above.

Fixes #5119

Props imath

[imath]

Replying to boonebgorges:

Good find, imath, and good patch.

I'm a little bit worried adding that the DISTINCT clause will have some edge-case effects on existing installations that are using certain kinds of filters on the sql query, and so I'm a little wary of putting this into the 1.8.1 release. On the other hand, the meta_query param is pretty broken without this fix. So, I'm going to go ahead and add it, and we'll see if the sky falls.

Thanks Boone :)

Meta_queries are really a great feature. I hope the sky will stay above our heads ;)