Stop search in POST post_type after update 3.5.0

  • Resolved xaja

    (@xaja)


    4 years, 8 months ago

    I have one custom post type — products and two defaults — page and post.

    so.

    3.4.2:
    in search i see results of ALL post types — products, page, post.

    3.5:
    in search i see results EXCEPT post. search word is in title or post_content (not even in custom fields), but it cant find it. if i deactivate your plugin (or install version 3.4.2) — is ok.

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Hi @xaja,

    Thank you for your message. First of all, I am asking you for patience – I do it out of passion in my free time, so please be patient.

    I will try to help you, but I need some extra things from you:

    1. Screenshot of your search results (entire browser window).
    2. SQL query displayed after change in plugin code (https://gbiorczyk.pl/img/8jYkbzF.png) when you try to search for something.
    3. ID of post you want to find.
    4. Screenshot of the edit page of the post you want to find.
    5. Database dump (only table wp_posts and wp_postmeta tables). Please delete private data from it, if any.

    Thread Starter xaja

    (@xaja)

    domain.ltd/?s=search

    3.4.2 (all working)
    SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta AS a ON (a.post_id = wp_posts.ID) INNER JOIN wp_postmeta AS b ON ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.post_id = wp_posts.ID)) INNER JOIN wp_posts AS c ON ((c.post_type = ‘acf-field’) AND ((c.post_content LIKE ‘%:”text”%’) OR (c.post_content LIKE ‘%:”textarea”%’) OR (c.post_content LIKE ‘%:”wysiwyg”%’))) WHERE 1=1 AND (((c.post_name = b.meta_value) AND (a.meta_value LIKE ‘%search%’)) OR ((wp_posts.post_title LIKE ‘%search%’) OR (wp_posts.post_content LIKE ‘%search%’) OR (wp_posts.post_excerpt LIKE ‘%search%’))) AND wp_posts.post_type IN (‘post’, ‘page’, ‘attachment’, ‘products’) AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘acf-disabled’ OR wp_posts.post_author = 1 AND wp_posts.post_status = ‘private’) ORDER BY wp_posts.post_date DESC LIMIT 0, 10

    3.5 (no results from POST post_type)
    SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta AS a ON (a.post_id = wp_posts.ID) INNER JOIN wp_postmeta AS b ON ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.meta_key LIKE CONCAT(‘\_’, a.meta_key))) INNER JOIN wp_posts AS c ON ((c.post_name = b.meta_value) AND (c.post_type = ‘acf-field’) AND ((c.post_content LIKE ‘%:”text”%’) OR (c.post_content LIKE ‘%:”textarea”%’) OR (c.post_content LIKE ‘%:”wysiwyg”%’))) WHERE 1=1 AND ((a.meta_value LIKE ‘%search%’) OR ((wp_posts.post_title LIKE ‘%search%’) OR (wp_posts.post_content LIKE ‘%search%’) OR (wp_posts.post_excerpt LIKE ‘%search%’))) AND wp_posts.post_type IN (‘post’, ‘page’, ‘attachment’, ‘products’) AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘acf-disabled’ OR wp_posts.post_author = 1 AND wp_posts.post_status = ‘private’) ORDER BY wp_posts.post_date DESC LIMIT 0, 10

    if you want, i can give you access to ftp and database in private.
    or i just wait another person with this bug )

    p.s. 3.5 is REALLY faster, thank you!

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Can you edit two plugin files?

    /wp-content/plugins/acf-better-search/app/Search/Join.php:

    <?php

  namespace AcfBetterSearch\Search;

  class Join
  {
    private $config, $wpdb;

    public function __construct()
    {
      add_filter('posts_join', [$this, 'sqlJoin'], 10, 2);
    }

    /* ---
      Functions
    --- */

    private function loadSettings()
    {
      if ($this->wpdb && $this->config) return;

      global $wpdb;
      $this->wpdb   = $wpdb;
      $this->config = apply_filters('acfbs_config', []);
    }

    public function sqlJoin($join, $query)
    {
      if (!isset($query->query_vars['s']) || empty($query->query_vars['s'])
        || !apply_filters('acfbs_search_is_available', true, $query)) return $join;

      $this->loadSettings();
      if (!$this->config['lite_mode'] && !$this->config['fields_types']) return $join;

      $parts   = [];
      $parts[] = sprintf(
        'INNER JOIN %s AS a ON (a.post_id = %s.ID)',
        $this->wpdb->postmeta,
        $this->wpdb->posts
      );
      $parts[] = sprintf(
        'INNER JOIN %s AS b ON ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.post_id = %s.ID))',
        $this->wpdb->postmeta,
        $this->wpdb->posts
      );

      if ($conditions = $this->getFieldsConditions()) {
        $parts[] = sprintf(
          'INNER JOIN %s AS c ON %s',
          $this->wpdb->posts,
          $conditions
        );
      }

      if ($this->checkFileFieldConditions()) {
        $parts[] = sprintf(
          'LEFT JOIN %s AS d ON (d.ID = a.meta_value)',
          $this->wpdb->posts
        );
      }

      $join .= ' ' . implode(' ', $parts) . ' ';
      return $join;
    }

    private function getFieldsConditions()
    {
      if ($this->config['lite_mode']) return null;

      $list   = [];
      $list[] = '(c.post_name = b.meta_value)';
      $list[] = '(c.post_type = \'acf-field\')';
      $list[] = $this->getFieldsTypes();

      $list = '(' . implode(' AND ', $list) . ')';
      return $list;
    }

    private function getFieldsTypes()
    {
      if ($this->config['selected_mode']) return '(c.post_content LIKE \'%s:18:"acfbs_allow_search";i:1;%\')';

      $list = [];
      foreach ($this->config['fields_types'] as $type) {
        $list[] = '(c.post_content LIKE \'%:"' . $type . '"%\')';
      }
      return '(' . implode(' OR ', $list) . ')';
    }

    private function checkFileFieldConditions()
    {
      if ($this->config['lite_mode'] || $this->config['selected_mode']
        || !in_array('file', $this->config['fields_types'])) return false;

      return true;
    }
  }

    /wp-content/plugins/acf-better-search/app/Search/Where.php:

    <?php

  namespace AcfBetterSearch\Search;

  class Where
  {
    private $config, $wpdb;

    public function __construct()
    {
      add_filter('posts_search', [$this, 'sqlWhere'], 0, 2); 
    }

    /* ---
      Functions
    --- */

    private function loadSettings()
    {
      if ($this->wpdb && $this->config) return;

      global $wpdb;
      $this->wpdb   = $wpdb;
      $this->config = apply_filters('acfbs_config', []);
    }

    public function sqlWhere($where, $query)
    {
      if (!isset($query->query_vars['s']) || empty($query->query_vars['s'])
        || !apply_filters('acfbs_search_is_available', true, $query)) return $where;

      $this->loadSettings();

      $list   = [];
      $list[] = $this->getACFConditions($query->query_vars['s']);
      $list[] = $this->getDefaultWordPressConditions($query->query_vars['s']);

      if (in_array('file', $this->config['fields_types'])) {
        $list[] = $this->getFileConditions($query->query_vars['s']);
      }

      $where = ' AND (' . implode(' OR ', $list) . ') ';
      return $where;
    }

    private function getACFConditions($words)
    {
      if (!$this->config['fields_types'] && !$this->config['lite_mode']) return '(1 = 2)';

      $words = !$this->config['whole_phrases'] ? explode(' ', $words) : [$words];
      $list  = [];

      foreach ($words as $word) {
        $word = addslashes($word);

        if ($this->config['whole_words']) $list[] = 'a.meta_value REGEXP \'[[:<:]]' . $word . '[[:>:]]\'';
        else $list[] = 'a.meta_value LIKE \'%' . $word . '%\'';
      }

      return sprintf('((b.meta_key LIKE CONCAT(\'\_\', a.meta_key)) AND (%s))',
        implode(') AND (', $list));
    }

    private function getDefaultWordPressConditions($words)
    {
      $words   = !$this->config['whole_phrases'] ? explode(' ', $words) : [$words];
      $columns = apply_filters('acfbs_search_post_object_fields', ['post_title', 'post_content', 'post_excerpt']);
      $list    = [];

      foreach ($words as $word) {
        $word       = addslashes($word);
        $conditions = [];

        foreach ($columns as $column) {
          $conditions[] = sprintf(
            ($this->config['whole_words']) ? '(%s.%s REGEXP %s)' : '(%s.%s LIKE %s)',
            $this->wpdb->posts,
            $column,
            ($this->config['whole_words']) ? ('\'[[:<:]]' . $word . '[[:>:]]\'') : ('\'%' . $word . '%\'')
          );
        }

        $list[] = '(' . implode(' OR ', $conditions) . ')';
      }

      if (count($list) > 1) $list = '(' . implode(' AND ', $list) . ')';
      else $list = $list[0];

      return $list;
    }

    private function getFileConditions($words)
    {
      $words = !$this->config['whole_phrases'] ? explode(' ', $words) : [$words];
      $list  = [];

      foreach ($words as $word) {
        $word   = addslashes($word);
        $list[] = 'd.post_title LIKE \'%' . $word . '%\'';

        if ($this->config['whole_words']) $list[] = 'd.post_title REGEXP \'[[:<:]]' . $word . '[[:>:]]\'';
        else $list[] = 'd.post_title LIKE \'%' . $word . '%\'';
      }

      $list = '(' . implode(') AND (', $list) . ')';
      return $list;
    }
  }

    Is it working now? How do you rate performance now?

    Thread Starter xaja

    (@xaja)

    Unfortunately, this code dont solve problem (
    posts from POSTS still not in search

    here new query with this edits:
    SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta AS a ON (a.post_id = wp_posts.ID) INNER JOIN wp_postmeta AS b ON ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.post_id = wp_posts.ID)) INNER JOIN wp_posts AS c ON ((c.post_name = b.meta_value) AND (c.post_type = 'acf-field') AND ((c.post_content LIKE '%:"text"%') OR (c.post_content LIKE '%:"textarea"%') OR (c.post_content LIKE '%:"wysiwyg"%'))) WHERE 1=1 AND (((b.meta_key LIKE CONCAT('\_', a.meta_key)) AND (a.meta_value LIKE '%search%')) OR ((wp_posts.post_title LIKE '%search%') OR (wp_posts.post_content LIKE '%search%') OR (wp_posts.post_excerpt LIKE '%search%'))) AND wp_posts.post_type IN ('post', 'page', 'attachment', 'products') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') ORDER BY wp_posts.post_date DESC LIMIT 0, 10

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Thank you for your tests. Could you edit again two plugin files?

    /wp-content/plugins/acf-better-search/app/Search/Join.php:

    <?php

  namespace AcfBetterSearch\Search;

  class Join
  {
    private $config, $wpdb;

    public function __construct()
    {
      add_filter('posts_join', [$this, 'sqlJoin'], 10, 2);
    }

    /* ---
      Functions
    --- */

    private function loadSettings()
    {
      if ($this->wpdb && $this->config) return;

      global $wpdb;
      $this->wpdb   = $wpdb;
      $this->config = apply_filters('acfbs_config', []);
    }

    public function sqlJoin($join, $query)
    {
      if (!isset($query->query_vars['s']) || empty($query->query_vars['s'])
        || !apply_filters('acfbs_search_is_available', true, $query)) return $join;

      $this->loadSettings();
      if (!$this->config['lite_mode'] && !$this->config['fields_types']) return $join;

      $parts   = [];
      $parts[] = sprintf(
        'INNER JOIN %s AS a ON (a.post_id = %s.ID)',
        $this->wpdb->postmeta,
        $this->wpdb->posts
      );
      $parts[] = sprintf(
        'LEFT JOIN %s AS b ON ((b.meta_id = a.meta_id + @@auto_increment_increment) AND (b.meta_key LIKE CONCAT(\'\_\', a.meta_key)))',
        $this->wpdb->postmeta,
        $this->wpdb->posts
      );

      if ($conditions = $this->getFieldsConditions()) {
        $parts[] = sprintf(
          'LEFT JOIN %s AS c ON %s',
          $this->wpdb->posts,
          $conditions
        );
      }

      if ($this->checkFileFieldConditions()) {
        $parts[] = sprintf(
          'LEFT JOIN %s AS d ON (d.ID = a.meta_value)',
          $this->wpdb->posts
        );
      }

      $join .= ' ' . implode(' ', $parts) . ' ';
      return $join;
    }

    private function getFieldsConditions()
    {
      if ($this->config['lite_mode']) return null;

      $list   = [];
      $list[] = '(c.post_name = b.meta_value)';
      $list[] = '(c.post_type = \'acf-field\')';
      $list[] = $this->getFieldsTypes();

      $list = '(' . implode(' AND ', $list) . ')';
      return $list;
    }

    private function getFieldsTypes()
    {
      if ($this->config['selected_mode']) return '(c.post_content LIKE \'%s:18:"acfbs_allow_search";i:1;%\')';

      $list = [];
      foreach ($this->config['fields_types'] as $type) {
        $list[] = '(c.post_content LIKE \'%:"' . $type . '"%\')';
      }
      return '(' . implode(' OR ', $list) . ')';
    }

    private function checkFileFieldConditions()
    {
      if ($this->config['lite_mode'] || $this->config['selected_mode']
        || !in_array('file', $this->config['fields_types'])) return false;

      return true;
    }
  }

    /wp-content/plugins/acf-better-search/app/Search/Where.php:

    <?php

  namespace AcfBetterSearch\Search;

  class Where
  {
    private $config, $wpdb;

    public function __construct()
    {
      add_filter('posts_search', [$this, 'sqlWhere'], 0, 2); 
    }

    /* ---
      Functions
    --- */

    private function loadSettings()
    {
      if ($this->wpdb && $this->config) return;

      global $wpdb;
      $this->wpdb   = $wpdb;
      $this->config = apply_filters('acfbs_config', []);
    }

    public function sqlWhere($where, $query)
    {
      if (!isset($query->query_vars['s']) || empty($query->query_vars['s'])
        || !apply_filters('acfbs_search_is_available', true, $query)) return $where;

      $this->loadSettings();

      $list   = [];
      $list[] = $this->getACFConditions($query->query_vars['s']);
      $list[] = $this->getDefaultWordPressConditions($query->query_vars['s']);

      if (in_array('file', $this->config['fields_types'])) {
        $list[] = $this->getFileConditions($query->query_vars['s']);
      }

      $where = ' AND (' . implode(' OR ', $list) . ') ';
      return $where;
    }

    private function getACFConditions($words)
    {
      if (!$this->config['fields_types'] && !$this->config['lite_mode']) return '(1 = 2)';

      $words = !$this->config['whole_phrases'] ? explode(' ', $words) : [$words];
      $list  = [];

      foreach ($words as $word) {
        $word = addslashes($word);

        if ($this->config['whole_words']) $list[] = 'a.meta_value REGEXP \'[[:<:]]' . $word . '[[:>:]]\'';
        else $list[] = 'a.meta_value LIKE \'%' . $word . '%\'';
      }

      return sprintf('((b.meta_id IS NOT NULL) %s AND (%s))',
        (!$this->config['lite_mode']) ? 'AND (c.ID IS NOT NULL)' : '',
        implode(') AND (', $list));
    }

    private function getDefaultWordPressConditions($words)
    {
      $words   = !$this->config['whole_phrases'] ? explode(' ', $words) : [$words];
      $columns = apply_filters('acfbs_search_post_object_fields', ['post_title', 'post_content', 'post_excerpt']);
      $list    = [];

      foreach ($words as $word) {
        $word       = addslashes($word);
        $conditions = [];

        foreach ($columns as $column) {
          $conditions[] = sprintf(
            ($this->config['whole_words']) ? '(%s.%s REGEXP %s)' : '(%s.%s LIKE %s)',
            $this->wpdb->posts,
            $column,
            ($this->config['whole_words']) ? ('\'[[:<:]]' . $word . '[[:>:]]\'') : ('\'%' . $word . '%\'')
          );
        }

        $list[] = '(' . implode(' OR ', $conditions) . ')';
      }

      if (count($list) > 1) $list = '(' . implode(' AND ', $list) . ')';
      else $list = $list[0];

      return $list;
    }

    private function getFileConditions($words)
    {
      $words = !$this->config['whole_phrases'] ? explode(' ', $words) : [$words];
      $list  = [];

      foreach ($words as $word) {
        $word   = addslashes($word);
        $list[] = 'd.post_title LIKE \'%' . $word . '%\'';

        if ($this->config['whole_words']) $list[] = 'd.post_title REGEXP \'[[:<:]]' . $word . '[[:>:]]\'';
        else $list[] = 'd.post_title LIKE \'%' . $word . '%\'';
      }

      $list = '(' . implode(') AND (', $list) . ')';
      return $list;
    }
  }

    Is it working now? How do you rate performance now?

    Thread Starter xaja

    (@xaja)

    YES!

    now works, and performance is very good!

    thanks!

    you will do an update like 3.5.1?

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Yes, the update is now available. Thank you very much for your help!

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Stop search in POST post_type after update 3.5.0’ is closed to new replies.

Tags