Changeset 29940 for trunk/tests/phpunit/tests/meta/query.php

Timestamp:

10/17/2014 08:20:04 PM (11 years ago)

Author:

boonebgorges

Message:

Overhaul SQL generating logic in WP_Meta_Query to avoid unnecessary table joins.

The logic used to generate clause SQL in WP_Meta_Query is somewhat arcane,
stemming mostly from an ongoing effort to eliminate costly table joins when
they are not necessary. By systematizing the process of looking for shareable
joins - as was done in WP_Tax_Query [29902] - it becomes possible to simplify
the construction of SQL queries in get_sql_for_clause(). Moreover, the
simplified logic is actually considerably better at identifying shareable
joins, such that certain uses of WP_Meta_Query will see joins reduced by 50%
or more.

Includes integration tests for a representative cross-section of the query
clause combinations that result in shared table aliases.

Props boonebgorges, sc0ttkclark.
See #24093.

File:

• trunk/tests/phpunit/tests/meta/query.php (modified) (3 diffs)

trunk/tests/phpunit/tests/meta/query.php

@@ -74 +74 @@
         $sql = $query->get_sql( 'post', $wpdb->posts, 'ID' );
 
-        $this->assertEquals( 2, substr_count( $sql['join'], 'INNER JOIN' ) );
+        $this->assertEquals( 1, substr_count( $sql['join'], 'INNER JOIN' ) );
     }
 
@@ -477 +477 @@
         $sql = $query->get_sql( 'post', $wpdb->posts, 'ID', $this );
 
-        // We should have 2 joins - one for my_first_key and one for my_second_key
-        $this->assertEquals( 2, substr_count( $sql['join'], 'JOIN' ) );
-
-        // The WHERE should check my_third_key against an unaliased table
-        $this->assertEquals( 1, substr_count( $sql['where'], "$wpdb->postmeta.meta_key = 'my_third_key'" ) );
-
+        $this->assertEquals( 3, substr_count( $sql['join'], 'JOIN' ) );
     }
 
@@ -547 +542 @@
         $this->assertSame( 0, substr_count( $sql['where'], "$wpdb->postmeta.meta_key = 'baz'" ) );
 
-        // When a value exists, it's not a key-only query
-        $this->assertSame( 0, substr_count( $sql['where'], "$wpdb->postmeta.meta_key = 'barry'" ) );
-
         // 'AND' queries don't have key-only queries
         $query2 = new WP_Meta_Query( array(