Skip to:
Content

Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#5824 closed defect (bug) (fixed)

Groups Meta Query, inconsistent results considering the meta query args

Reported by: imath Owned by: boonebgorges
Milestone: 2.1 Priority: high
Severity: normal Version: 1.8
Component: Groups Keywords: has-patch
Cc:

Description

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 :)

Attachments (4)

5824.patch (783 bytes) - added by imath 4 years ago.
5824.unit-test.patch (1.4 KB) - added by boonebgorges 4 years ago.
5824.unit_test.02.patch (1.4 KB) - added by imath 4 years ago.
5824.02.patch (2.5 KB) - added by imath 4 years ago.

Download all attachments as: .zip

Change History (16)

@imath
4 years ago

#1 @DJPaul
4 years ago

  • Keywords needs-unit-tests added

#2 @boonebgorges
4 years ago

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?

#3 @imath
4 years ago

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

#4 @imath
4 years ago

  • Keywords has-patch removed

#5 @imath
4 years ago

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.

#6 @imath
4 years ago

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

#7 @boonebgorges
4 years ago

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

@imath
4 years ago

#8 @imath
4 years ago

  • Keywords has-patch added; needs-unit-tests removed

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 ;)

#9 @boonebgorges
4 years ago

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.

#10 follow-up: @boonebgorges
4 years ago

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

#11 @boonebgorges
4 years ago

  • Owner set to boonebgorges
  • Resolution set to fixed
  • Status changed from new to closed

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

#12 in reply to: ↑ 10 @imath
4 years ago

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 :)

Note: See TracTickets for help on using tickets.