Groups Meta Query, inconsistent results considering the meta query args

[imath]

Currently working on a project where i use Group Meta queries. I've noticed that the meta_key is not parsed and not returned in the $sql_array['where'] part built in BP_Groups_group::get_meta_query_sql()

Let's take an example, i want to check if groups have activated a group extension. The groupmeta could be '_my_group_extension_active' having a value set to 1 if active, 0 otherwise.

if i user this meta_query to get these groups

$groups_query['meta_query'] = array(
	array(
		'key'     => '_my_group_extension_active',
		'value'   =>  1,
		'compare' => '='
	)
);

Then, it will return all groups that have a meta_value set to 1 but not the groups that have a meta_key set to '_my_group_extension_active' and a meta_value set to 1. So there's a big chance if another meta_key has a value set to 1 (for example total_member_count) that the results are inconsistent.

To make sure meta_key is parsed in BP_Groups_group::get_meta_query_sql(), i think we need to loop in the $meta_query_where_clauses to build the $sql_array['where'] part. See attached patch which is working in my case.

It would be great if fixed in 2.1 :)

[boonebgorges]

I can't reproduce this. Unit test attached (5824.unit-test.patch). imath, can you verify that I'm testing it correctly?

Reproducibility aside, I'm not sure I understand why we'd need to do this double looping. The WHERE clauses should be independent of each other; there should be no case where multiple groupmeta table references appear in a single meta query where clause. Maybe you can share more of the details? What does the problematic SQL query end up looking like?

[imath]

argh, my patch is not good, it fails as soon as there more than one meta query :(

[imath]

This is giving me headache!

ok here the $sql_array returned when using the meta query in my example :

array(
 'join'  => 'wp_bp_groups_groupmeta, '
 'where' => " AND ( (g.id = wp_bp_groups_groupmeta.group_id AND CAST(wp_bp_groups_groupmeta.meta_value AS CHAR) = '1') )"
)

As you can see the where clause is missing the meta_key, my patch was changing the $sql_array to this :

array(
  'join'  => 'wp_bp_groups_groupmeta, '
  'where' => " AND ( (g.id = wp_bp_groups_groupmeta.group_id AND CAST(wp_bp_groups_groupmeta.meta_value AS CHAR) = '1') ) AND (g.id = wp_bp_groups_groupmeta.group_id AND wp_bp_groups_groupmeta.meta_key = '_my_group_extension_active' )"
)

But it's not good as soon as we have more than one meta_query clause and it duplicates g.id = wp_bp_groups_groupmeta.group_id

I'm testing with 3 groups

• the 3 groups have a _my_group_extension_active meta_key set
• 2 have it set to 1
• 1 have it set to 0

I should have only 2 groups but i have three, because the total_members_count meta key of last group is 1

So it looks like the group meta query is only looking for a meta_value instead of looking for a meta_key having a meta_value.

[imath]

5824.unit_test.02.patch should return 2 groups instead of 1

[boonebgorges]

Thanks, imath. The 'compare' param is what seems to be throwing it off. Let me have a look.

[imath]

Ok, i think we don't need to do the foreach/preg_replace part.

In 5824.02.patch i simply join the g.id = wp_bp_groups_groupmeta[_aliases].group_id clauses (one for each clauses of the meta query) at the end of the $meta_sql['where'] and it seems to fix the issue.

Ran the phpunit --group group_meta_query with success including the one i've added in the patch ;)

[boonebgorges]

Brilliant. Thanks, imath.

I've been writing some other tests and there's still one that doesn't pass with your patch. I'm going to mess with it some more to see if I can figure it out. If not, yours is still an improvement (the problematic test fails on current trunk as well) so I'll commit it.

[boonebgorges]

Turns out that the problematic test I'd written - which tested your fix against multiple clauses using 'compare' => '=' - is a WordPress bug. I've opened a ticket. ​https://core.trac.wordpress.org/ticket/29285

[boonebgorges]

In 8949:

Simplify logic that transforms WP_Meta_Query SQL for Groups queries

This simplified logic fixes a bug related to the use of the 'compare'
operator in certain cases.

Fixes #5824

Props imath

[imath]

Replying to boonebgorges:

Turns out that the problematic test I'd written - which tested your fix against multiple clauses using 'compare' => '=' - is a WordPress bug. I've opened a ticket. ​https://core.trac.wordpress.org/ticket/29285

Yes i was still searching and thought it might be a WordPress one ;) You just confirmed it was the case, thanks a lot for your researches, commit and fix :)