[TikiWiki-commits] [Git][tikiwiki/tiki][29.x] [BP][FIX][ENH] Search: Fix failures when using MySQL native stopwords with AND operator

MAGENE Sem Joel pushed to branch 29.x at Tiki Wiki CMS Groupware / Tiki


Commits:
3e2bdd5a by MAGENE Sem Joel at 2025-10-10T20:47:00+00:00
[BP][FIX][ENH] Search: Fix failures when using MySQL native stopwords with AND operator
---
* [FIX][ENH] Search: Fix failures when using MySQL native stopwords with AND operator
---
* [FIX][ENH] Search: Fix failures when using MySQL native stopwords with AND operator

See merge request tikiwiki/tiki!8603

(cherry picked from commit 2325502aaa4d3fb12c6c7236d04e74ad3991320f)

See merge request tikiwiki/tiki!8757

- - - - -


4 changed files:

- lib/core/Search/MySql/FieldQueryBuilder.php
- lib/core/Search/MySql/QueryBuilder.php
- lib/core/Search/MySql/Table.php
- lib/prefs/unified.php


Changes:

=====================================
lib/core/Search/MySql/FieldQueryBuilder.php
=====================================
@@ -15,17 +15,49 @@ class Search_MySql_FieldQueryBuilder
     private $invert = false;
     private $boolean_or = ' ';
     private $escapeCallback;
+    private $stopwords = [];
+    private $minTokenSize = 3;
+
+    /**
+    * Sets the list of stopwords to be ignored during query building.
+    *
+    * @param array $stopwords The list of stopword strings.
+    */
+    public function setStopwords(array $stopwords)
+    {
+        $this->stopwords = array_map('strtolower', $stopwords);
+    }
+
+    /**
+    * Sets the minimum word length for a token to be included in the query.
+    * This should match the @@innodb_ft_min_token_size setting of the MySQL server.
+    *
+    * @param int $size The minimum number of characters a word must have.
+    */
+    public function setMinTokenSize(int $size)
+    {
+        $this->minTokenSize = $size;
+    }
 
     public function build(Search_Expr_Interface $expr, Search_Type_Factory_Interface $factory)
     {
         $invert = false;
         $string = $expr->walk(
             function ($node, $childNodes) use ($factory, &$invert) {
+                $childNodes = array_filter($childNodes);
                 if ($node instanceof Token) {
                     $string = $node->getValue($factory)->getValue();
                     if (is_array($string)) {
                         $string = implode(' ', $string);
                     }
+                    // If it's too short, treat it as empty.
+                    if (mb_strlen($string) < $this->minTokenSize) {
+                        return '';
+                    }
+                    // If it's a stopword, treat it as an empty token.
+                    if (in_array(strtolower($string), $this->stopwords, true)) {
+                        return '';
+                    }
                     if ($this->escapeCallback) {
                         $string = call_user_func($this->escapeCallback, $string);
                     }


=====================================
lib/core/Search/MySql/QueryBuilder.php
=====================================
@@ -21,6 +21,8 @@ class Search_MySql_QueryBuilder
     private $fieldBuilder;
     private $tfTranslator;
     private $indexes = [];
+    private $stopwords = null;
+    private $minTokenSize = null;
 
     public function __construct($db, Search_MySql_Table|null $table = null)
     {
@@ -31,6 +33,23 @@ class Search_MySql_QueryBuilder
         $this->tfTranslator = new Search_MySql_TrackerFieldTranslator();
     }
 
+    /**
+    * Gets the list of stopwords from Tiki preferences.
+    * It caches the result locally to avoid repeated lookups.
+    *
+    * @return array The list of stopwords.
+    */
+    private function getStopwords()
+    {
+        if ($this->stopwords === null) {
+            global $prefs;
+            $this->stopwords = ! empty($prefs['unified_stopwords']) && is_array($prefs['unified_stopwords'])
+                ? $prefs['unified_stopwords']
+                : [];
+        }
+        return $this->stopwords;
+    }
+
     public function build(Search_Expr_Interface $expr)
     {
         $this->indexes = [];
@@ -44,6 +63,20 @@ class Search_MySql_QueryBuilder
         return array_values($this->indexes);
     }
 
+    /**
+    * Gets the minimum word length for InnoDB Full-Text Search.
+    * This value is fetched directly from the database and cached locally.
+    *
+    * @return int The value of @@innodb_ft_min_token_size.
+    */
+    private function getMinTokenSize()
+    {
+        if ($this->minTokenSize === null) {
+            $this->minTokenSize = (int) $this->db->getOne("SELECT @@innodb_ft_min_token_size");
+        }
+        return $this->minTokenSize;
+    }
+
     public function __invoke($node, $childNodes)
     {
         $exception = null;
@@ -71,11 +104,20 @@ class Search_MySql_QueryBuilder
             if (! $node instanceof NotX && count($fields) == 1 && $this->isFullText($node)) {
                 // $query contains the token string to compare against $fields[0] in the unified search table
                 // $fields[0] can be i.e  'allowed_users', 'allowed_groups'
+                $this->fieldBuilder->setStopwords($this->getStopwords());
+                $this->fieldBuilder->setMinTokenSize($this->getMinTokenSize());
                 $query = $this->fieldBuilder->build($node, $this->factory);
+
+                // If the query is empty, it only contained stopwords.
+                if (empty($query) && ! $node instanceof MoreLikeThis) {
+                    return '';
+                }
+
                 if ($node instanceof MoreLikeThis) {
                     $type = $node->getObjectType();
                     $object = $node->getObjectId();
-                    $str = $node->getContent() ?: $this->getDocumentContent($type, $object);
+                    $field = $node->getField();
+                    $str = $node->getContent() ?: $this->getDocumentContent($type, $object, $field);
                 } else {
                     $str = $this->db->qstr($query);
                 }
@@ -180,12 +222,12 @@ class Search_MySql_QueryBuilder
         return $node->getValue($this->factory)->getValue();
     }
 
-    private function getDocumentContent($type, $object)
+    private function getDocumentContent($type, $object, $field)
     {
-        $results = $this->table->fetchAllIndex(['contents'], ['object_type' => $type, 'object_id' => $object]);
+        $results = $this->table->fetchAllIndex([$field], ['object_type' => $type, 'object_id' => $object]);
 
-        if (! empty($results[0]['contents'])) {
-            return $this->db->qstr($results[0]['contents']);
+        if (! empty($results[0][$field])) {
+            return $this->db->qstr($results[0][$field]);
         }
 
         return '';


=====================================
lib/core/Search/MySql/Table.php
=====================================
@@ -62,11 +62,16 @@ class Search_MySql_Table extends TikiDb_Table
 
     public function drop()
     {
+        $stopwordTableName = $this->tableName . '_stopwords';
+        $escapedStopwordTable = $this->escapeIdentifier($stopwordTableName);
+        $this->db->query("DROP TABLE IF EXISTS $escapedStopwordTable", options: [TikiDB::QUERY_OPTION_LOG_GROUP => self::UNIFIED_MYSQL_WRITE_LOG_GROUP]);
+
         $tables = $this->indexTables();
         foreach ($tables as $table) {
             $table = $this->escapeIdentifier($table);
             $this->db->query("DROP TABLE IF EXISTS $table", options: [TikiDB::QUERY_OPTION_LOG_GROUP => self::UNIFIED_MYSQL_WRITE_LOG_GROUP]);
         }
+
         $this->definition = false;
         $this->exists = false;
 
@@ -298,9 +303,13 @@ class Search_MySql_Table extends TikiDb_Table
             }
             $tables = [$tableName];
             $result = $this->db->fetchAll("SHOW TABLES LIKE '" . $tableName . "_%'", options: [TikiDB::QUERY_OPTION_LOG_GROUP => self::UNIFIED_MYSQL_READ_LOG_GROUP]);
-            foreach ($result as $row) {
-                $tables[] = array_shift($row);
-            }
+
+            $partitions = array_filter(
+                array_map(fn($row) => array_shift($row), $result),
+                fn($table) => preg_match('/_[0-9]+$/', $table)
+            );
+
+            $tables = array_merge($tables, $partitions);
         }
         return $tables;
     }
@@ -355,6 +364,7 @@ class Search_MySql_Table extends TikiDb_Table
         );
         $this->exists = true;
 
+        $this->setupStopwordTable();
         $this->emptyBuffer();
     }
 
@@ -416,13 +426,42 @@ class Search_MySql_Table extends TikiDb_Table
         $this->schemaBuffer->push("ADD INDEX $escapedIndex ($escapedField)");
     }
 
+    /**
+    * Creates and populates a custom stopword table for the current index.
+    * This allows Tiki to control InnoDB's stopword list directly during index creation.
+    * This method is called right before a FULLTEXT index is added.
+    *
+    * @return string The name of the created stopword table.
+    */
+    private function setupStopwordTable(): string
+    {
+        global $prefs;
+
+        $stopwordTableName = $this->tableName . '_stopwords';
+
+        $this->db->query("DROP TABLE IF EXISTS `{$stopwordTableName}`");
+        $this->db->query("CREATE TABLE `{$stopwordTableName}` (value VARCHAR(30)) ENGINE=INNODB");
+        $stopwords = $prefs['unified_stopwords'] ?? [];
+        if (! empty($stopwords)) {
+            $stopwordTable = $this->db->table($stopwordTableName, false);
+            foreach ($stopwords as $word) {
+                $stopwordTable->insert(['value' => $word]);
+            }
+        }
+
+        return $stopwordTableName;
+    }
+
     private function addFullText($fieldName)
     {
+        $stopwordTableName = $this->setupStopwordTable();
+        $dbName = $this->db->getOne("SELECT DATABASE()");
+        $this->db->query("SET SESSION innodb_ft_user_stopword_table = ?", ["{$dbName}/{$stopwordTableName}"]);
+
         $table = $this->escapeIdentifier($this->definition[$fieldName]['table']);
         $this->schemaBuffer->setPrefix("ALTER TABLE $table ");
 
         $indexName = $fieldName . '_fulltext';
-        $table = $this->escapeIdentifier($this->tableName);
         $escapedIndex = $this->escapeIdentifier($this->tfTranslator->shortenize($indexName));
         $escapedField = $this->escapeIdentifier($this->tfTranslator->shortenize($fieldName));
 


=====================================
lib/prefs/unified.php
=====================================
@@ -390,7 +390,7 @@ function prefs_unified_list()
             'type' => 'text',
             'default' => ["a", "an", "and", "are", "as", "at", "be", "but", "by", "for", "if", "in", "into", "is", "it", "not", "of", "on", "or", "s", "such", "t", "that", "the", "their", "then", "there", "these", "they", "this", "to", "was", "will", "with"],
             'separator' => ',',
-            'hint' => tr('MySQL full-text search has its own list of stop words configured in the server.'),
+            'hint' => tr('This list is applied to the selected search engine. Note for MySQL: It completely replaces the native InnoDB stopword list. If left empty, will disable stopword filtering.'),
         ],
         'unified_trim_sorted_search' => [
             'name' => tra('Automatically trim Elasticsearch results on date-sorted query'),



View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/3e2bdd5a011a363a200e494fab2c8df7b991e831

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/3e2bdd5a011a363a200e494fab2c8df7b991e831
You're receiving this email because of your account on gitlab.com.

_______________________________________________
TikiWiki-cvs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs