SupportsAdvancedSearch breaks in the presence of existing wp_postmeta JOIN

[acobster]

Search breaks e.g. on The Events Calendar, which already JOINs the wp_postmeta table for Event queries. We should add a default alias for this table instead of always JOINing it directly. Allow alias override via $config passed to configure_advanced_search().

[acobster]

Downstream changes made as a bandaid:

diff --git a/wp-content/plugins/conifer/lib/Conifer/Post/SupportsAdvancedSearch.php b/wp-content/plugins/conifer/lib/Conifer/Post/SupportsAdvancedSearch.php
index 5ea498d6..40b134fa 100644
--- a/wp-content/plugins/conifer/lib/Conifer/Post/SupportsAdvancedSearch.php
+++ b/wp-content/plugins/conifer/lib/Conifer/Post/SupportsAdvancedSearch.php
@@ -18,20 +18,39 @@ trait SupportsAdvancedSearch {
         return $clauses;
       }

+      $queryingPostTypes = $query->query_vars['post_type'] ?? [];
+      if (!is_array($queryingPostTypes)) {
+        $queryingPostTypes = [$queryingPostTypes];
+      }
+
+      $searchCustomizations = array_filter(
+        $config,
+        function($searchConfig) use($queryingPostTypes) {
+          return !empty(array_intersect(
+            $queryingPostTypes,
+            $searchConfig['post_type']
+          ));
+        });
+
+      if (empty($searchCustomizations)) {
+        // no advanced search customizations apply to this query
+        return $clauses;
+      }
+
       // ->prepend_distinct
       $clauses['fields'] = ' DISTINCT ' . $clauses['fields'];

       // ->add_join('postmeta', 'posts.ID = postmeta.post_id')
       $clauses['join'] .=
-        " LEFT JOIN {$wpdb->postmeta}"
-        . " ON ( {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id ) ";
+        " LEFT JOIN {$wpdb->postmeta} meta_search"
+        . " ON ( {$wpdb->posts}.ID = meta_search.post_id ) ";

       // map -> wildcard
       $terms = array_map(function(string $term) : string {
         return "%{$term}%";
       }, $query->query_vars['search_terms']);

-      $whereClauses = array_map(function(array $postTypeSearch) use($wpdb, $terms) {
+      $whereClauses = array_map(function(array $postTypeSearch) use($wpdb, $terms, $query) {
         $titleComparisons = array_map(function(string $term) use($wpdb) : string {
           return $wpdb->prepare("{$wpdb->posts}.post_title LIKE %s", $term);
         }, $terms);
@@ -47,14 +66,10 @@ trait SupportsAdvancedSearch {
         }, $terms);
         $contentClause = '(' . implode(' OR ', $contentComparisons) . ')';

-        $metaKeyComparisons = [
-          '(meta_key = "hello")',
-          '(meta_key LIKE "good%")',
-        ];
         $metaKeyComparisons = array_map(function($key) use($wpdb) : string {
           if (is_string($key)) {

-            return $wpdb->prepare('(meta_key = %s)', $key);
+            return $wpdb->prepare('(meta_search.meta_key = %s)', $key);

           } elseif (is_array($key) && isset($key['key'])) {

@@ -64,7 +79,7 @@ trait SupportsAdvancedSearch {
               $op = '=';
             }

-            return $wpdb->prepare("(meta_key {$op} %s)", $key['key']);
+            return $wpdb->prepare("(meta_search.meta_key {$op} %s)", $key['key']);
           }

           return '';
@@ -73,7 +88,7 @@ trait SupportsAdvancedSearch {
         $metaKeyClause = '(' . implode(' OR ', $metaKeyComparisons) . ')';

         $metaValueComparisons = array_map(function(string $term) use($wpdb) {
-          return $wpdb->prepare('(meta_value LIKE %s)', $term);
+          return $wpdb->prepare('(meta_search.meta_value LIKE %s)', $term);
         }, $terms);
         $metaValueClause = '(' . implode(' OR ', $metaValueComparisons) . ')';

@@ -82,17 +97,29 @@ trait SupportsAdvancedSearch {
         // put it all together
         $searchClauses = [$titleClause, $excerptClause, $contentClause, $metaClause];

-        // TODO default to get_post_types() or similar
-        $postTypes = $postTypeSearch['post_type'] ?? ['post', 'page'];
+        $queryPostType = $query->query_vars['post_type'];
+        $postTypes     = is_array($queryPostType)
+          ? $queryPostType
+          : [$queryPostType];
         $postTypeCriteria = array_map(function(string $type) use($wpdb) {
           return $wpdb->prepare('%s', $type);
         }, $postTypes);

-        // TODO default to get_post_statues() or similar
-        $postStatuses = $postTypeSearch['post_status'] ?? ['publish'];
-        $postStatusCriteria = array_map(function(string $type) use($wpdb) {
-          return $wpdb->prepare('%s', $type);
-        }, $postStatuses);
+        $queryStatuses = $query->query_vars['post_status'] ?? ['publish'];
+        if ($queryStatuses === 'any') {
+          $queryStatusClause = '';
+        } else {
+          $postStatuses = is_array($queryStatuses)
+            ? $queryStatuses
+            : [$queryStatuses];
+          $postStatusCriteria = array_map(function(string $type) use($wpdb) {
+            return $wpdb->prepare('%s', $type);
+          }, $postStatuses);
+
+          $queryStatusClause = ' AND wp_posts.post_status IN ('
+            . implode(', ', $postStatusCriteria)
+            . ')';
+        }

         return
           '('
@@ -101,13 +128,16 @@ trait SupportsAdvancedSearch {

           . ' AND wp_posts.post_type IN (' . implode(', ', $postTypeCriteria) . ')'

-          . ' AND wp_posts.post_status IN (' . implode(', ', $postStatusCriteria) . ')'
+          . $queryStatusClause

           . ')';
       }, $config);

       $clauses['where'] = ' AND (' . implode(' OR ', $whereClauses) . ')';

+      // TODO more sophisticated orderby
+      $clauses['orderby'] = 'wp_posts.post_title LIKE "%point%" DESC';
+
       return $clauses;
     }, 10, 2);
   }