Changeset 7322

Timestamp:

07/27/2013 06:06:17 PM (11 years ago)

Author:

boonebgorges

Message:

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

Location:

trunk

Files:

• bp-groups/bp-groups-classes.php (modified) (3 diffs)
• tests/testcases/groups/class-bp-groups-group.php (modified) (1 diff)

trunk/bp-groups/bp-groups-classes.php

@@ -354 +354 @@
         $total_sql = array();
 
-        $sql['select'] = "SELECT g.*, gm1.meta_value AS total_member_count, gm2.meta_value AS last_activity";
+        $sql['select'] = "SELECT DISTINCT g.id, g.*, gm1.meta_value AS total_member_count, gm2.meta_value AS last_activity";
         $sql['from']   = " FROM {$bp->groups->table_name_groupmeta} gm1, {$bp->groups->table_name_groupmeta} gm2,";
 
@@ -469 +469 @@
         if ( ! empty( $meta_query_sql['where'] ) ) {
             // Join the groupmeta table
-            $total_sql['select'] .= ", {$bp->groups->table_name_groupmeta} gmmq";
+            $total_sql['select'] .= ", ". substr( $meta_query_sql['join'], 0, -2 );
 
             // Modify the meta_query clause from paged_sql for our syntax
             $meta_query_clause = preg_replace( '/^\s*AND/', '', $meta_query_sql['where'] );
-            $meta_query_clause = str_replace( $bp->groups->table_name_groupmeta, 'gmmq', $meta_query_clause );
             $total_sql['where'][] = $meta_query_clause;
         }
@@ -561 +560 @@
             // the more general query syntax to accord better with
             // BP/WP convention
-            preg_match( '/INNER JOIN (.*) ON/', $meta_sql['join'], $matches_a );
-            preg_match( '/ON \((.*)\)$/', $meta_sql['join'], $matches_b );
+            preg_match_all( '/INNER JOIN (.*) ON/', $meta_sql['join'], $matches_a );
+            preg_match_all( '/ON \((.*)\)/', $meta_sql['join'], $matches_b );
+
             if ( ! empty( $matches_a[1] ) && ! empty( $matches_b[1] ) ) {
-                $sql_array['join']  = $matches_a[1] . ', ';
-                $sql_array['where'] = preg_replace( '/^(\sAND\s+[\(\s]+)/', '$1' . $matches_b[1] . ' AND ', $meta_sql['where'] );
+                $sql_array['join']  = implode( ',', $matches_a[1] ). ', ';
+
+                $sql_array['where'] = '';
+
+                $meta_query_where_clauses = explode( "\n", $meta_sql['where'] );
+                foreach( $matches_b[1] as $key => $group_id_clause ) {
+                    $sql_array['where'] .= ' ' . preg_replace( '/^(AND\s+[\(\s]+)/', '$1' . $group_id_clause . ' AND ', ltrim( $meta_query_where_clauses[ $key ] ) );
+                }
+
             }
         }

trunk/tests/testcases/groups/class-bp-groups-group.php

@@ -100 +100 @@
     /**
      * @group get
+     * @group group_meta_query
+     */
+    public function test_get_with_meta_query_multiple_clauses() {
+        $now = time();
+        $g1 = $this->factory->group->create( array(
+            'last_activity' => date( 'Y-m-d H:i:s', $now - 60*60 ),
+        ) );
+        $g2 = $this->factory->group->create( array(
+            'last_activity' => date( 'Y-m-d H:i:s', $now - 60*60*2 ),
+        ) );
+        $g3 = $this->factory->group->create( array(
+            'last_activity' => date( 'Y-m-d H:i:s', $now - 60*60*3 ),
+        ) );
+        groups_update_groupmeta( $g1, 'foo', 'bar' );
+        groups_update_groupmeta( $g2, 'foo', 'bar' );
+        groups_update_groupmeta( $g1, 'bar', 'barry' );
+
+        $groups = BP_Groups_Group::get( array(
+            'meta_query' => array(
+                'relation' => 'AND',
+                array(
+                    'key' => 'foo',
+                    'value' => 'bar',
+                ),
+                array(
+                    'key' => 'bar',
+                    'value' => 'barry',
+                ),
+            ),
+        ) );
+        $ids = wp_list_pluck( $groups['groups'], 'id' );
+        $this->assertEquals( $ids, array( $g1 ) );
+        $this->assertEquals( 1, $groups['total'] );
+    }
+
+    /**
+     * @group get
      */
     public function test_get_normal_search() {