[buddypress-trac] [BuddyPress Trac] #5874: BP_Groups_Group::get() returns incorrect results when using meta_query with multiple meta_values and OR relationship

[buddypress-trac]

#5874: BP_Groups_Group::get() returns incorrect results when using meta_query with
multiple meta_values and OR relationship
--------------------------+------------------------------
 Reporter:  richtelford   |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Groups        |     Version:
 Severity:  major         |  Resolution:
 Keywords:                |
--------------------------+------------------------------

Comment (by richtelford):

 Yes, I get that, and I agree that it's good to avoid.

 Great I wasn't sure if that's what you were meaning or not.

  But it is very complicated to change this syntax, because we have a
 variety of backward compatibility issues to solve first. See #5451.

 I understand.

 Here is the SQL output ($paged_groups_sql) before changing anything:

 {{{
 SELECT DISTINCT g.id, g.*, gm1.meta_value AS total_member_count,
 gm2.meta_value AS last_activity
 FROM wp_bp_groups_groupmeta gm1, wp_bp_groups_groupmeta
 gm2,wp_bp_groups_groupmeta,wp_bp_groups_groupmeta AS
 mt1,wp_bp_groups_groupmeta AS mt2, wp_bp_groups g WHERE g.id =
 gm1.group_id
 AND g.id = gm2.group_id
 AND gm2.meta_key = 'last_activity' A
 ND gm1.meta_key = 'total_member_count'
 AND g.status != 'hidden'
 AND ( (g.id = wp_bp_groups_groupmeta.group_id AND
 wp_bp_groups_groupmeta.meta_key = 'group_sport' AND
 CAST(wp_bp_groups_groupmeta.meta_value AS CHAR) LIKE '%mountain%') OR
 (mt1.meta_key = 'group_sport' AND CAST(mt1.meta_value AS CHAR) LIKE
 '%running%') OR (mt2.meta_key = 'group_sport' AND CAST(mt2.meta_value AS
 CHAR) LIKE '%winter%') )
 ORDER BY g.date_created DESC
 }}}

 You can see the problem in the second last line. The brackets don't form
 correctly around the arguments. Here is the SQL output after my changes -
 again I've padded this out to help readability:

 {{{
 SELECT DISTINCT g.id, g.*, gm1.meta_value AS total_member_count,
 gm2.meta_value AS last_activity
 FROM wp_bp_groups g
 INNER JOIN wp_bp_groups_groupmeta gm1 ON g.id = gm1.group_id
 INNER JOIN wp_bp_groups_groupmeta gm2 ON g.id = gm2.group_id
 INNER JOIN wp_bp_groups_groupmeta ON (g.id =
 wp_bp_groups_groupmeta.group_id)
 INNER JOIN wp_bp_groups_groupmeta AS mt1 ON (g.id = mt1.group_id)
 INNER JOIN wp_bp_groups_groupmeta AS mt2 ON (g.id = mt2.group_id)
 WHERE gm2.meta_key = 'last_activity'
 AND gm1.meta_key = 'total_member_count'
 AND g.status != 'hidden'
 AND ( (wp_bp_groups_groupmeta.meta_key = 'group_sport' AND
 CAST(wp_bp_groups_groupmeta.meta_value AS CHAR) LIKE '%mountain%') OR
 (mt1.meta_key = 'group_sport' AND CAST(mt1.meta_value AS CHAR) LIKE
 '%running%') OR (mt2.meta_key = 'group_sport' AND CAST(mt2.meta_value AS
 CHAR) LIKE '%winter%') )
 ORDER BY g.date_created DESC
 }}}

 Executing the first query returns 4 results (all groups I've created).
 Executing the second returns 2 results (there are no groups with
 group_sport = 'winter').

--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/5874#comment:11>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac