Changeset 7378

Timestamp:

07/06/2018 12:45:46 AM (8 years ago)

Author:

coreymckrill

Message:

WordCamp Reports: Switch the SQL back to NOT IN

@dd32 points out that FIND_IN_SET is potentially inefficient, and the real
issue was inserting the ID list into the query via a %s string token and
prepare. See:

​https://wordpress.slack.com/archives/C08M59V3P/p1530834434000167?thread_ts=1530832569.000030&cid=C08M59V3P

With this commit we're instead just putting the comma-separated list of
integers directly into the SQL without a token.

Location:

sites/trunk/wordcamp.org/public_html/wp-content/plugins/wordcamp-reports/classes/report

Files:

• class-payment-activity.php (modified) (1 diff)
• class-sponsor-invoices.php (modified) (1 diff)
• class-ticket-revenue.php (modified) (1 diff)

sites/trunk/wordcamp.org/public_html/wp-content/plugins/wordcamp-reports/classes/report/class-payment-activity.php

@@ -232 +232 @@
             );
         } else {
-            $extra_where = sprintf(
-                ' AND FIND_IN_SET( blog_id, %s ) = 0',
-                implode( ',', Reports\get_excluded_site_ids() )
-            );
+            $excluded_ids = implode( ',', array_map( 'absint', Reports\get_excluded_site_ids() ) );
+            $extra_where = " AND blog_id NOT IN ( $excluded_ids )";
         }
 

sites/trunk/wordcamp.org/public_html/wp-content/plugins/wordcamp-reports/classes/report/class-sponsor-invoices.php

@@ -234 +234 @@
             $where_values[] = $this->wordcamp_site_id;
         } else {
-            $where_clause[] = 'FIND_IN_SET( blog_id, %s ) = 0';
-            $where_values[] = implode( ',', Reports\get_excluded_site_ids() );
+            $excluded_ids = implode( ',', array_map( 'absint', Reports\get_excluded_site_ids() ) );
+            $where_clause[] = "blog_id NOT IN ( $excluded_ids )";
         }
 

sites/trunk/wordcamp.org/public_html/wp-content/plugins/wordcamp-reports/classes/report/class-ticket-revenue.php

@@ -256 +256 @@
             $where_values[] = $this->wordcamp_site_id;
         } else {
-            $where_clause[] = 'FIND_IN_SET( blog_id, %s ) = 0';
-            $where_values[] = implode( ',', Reports\get_excluded_site_ids() );
+            $excluded_ids = implode( ',', array_map( 'absint', Reports\get_excluded_site_ids() ) );
+            $where_clause[] = "blog_id NOT IN ( $excluded_ids )";
         }