flarum 中文搜尋問題 mysql 內建 fulltext 全文搜尋

林立興 linlihsin.com

meilisearch 的缺點就是 index 索引太肥大
跟 mysql 內建的索引比起來 ... 有人說差十倍 ...
加上不是每一台機器都可以裝 meilisearch
有的機器就只能用內建的 mysql fulltext

這時候就只能請 AI 修改 fulltext gambit
讓搜尋結果更完整的顯示出來
因為使用了 like %%
理論上會比較吃系統資源

請自行在中文搜尋結果跟系統效能之間抉擇
或是請 AI 幫你改成 like % 也可以
什麼都不改則是最安全的選項

public_html/vendor/flarum/core/js/dist
forum.js

用 notepad ++ 開啟
搜尋 MIN_SEARCH_LEN=3
改成 MIN_SEARCH_LEN=2

接著把 FulltextGambit.php 代碼換掉
我安裝的是 flarum-v1.x-no-public-dir-php8.3.zip
public_html/vendor/flarum/core/src/Discussion/Search/Gambit
FulltextGambit.php

<?php

namespace Flarum\Discussion\Search\Gambit;

use Flarum\Discussion\Discussion;
use Flarum\Post\Post;
use Flarum\Search\GambitInterface;
use Flarum\Search\SearchState;
use Illuminate\Database\Query\Expression;

class FulltextGambit implements GambitInterface
{
    public function apply(SearchState $search, $bit)
    {
        $keywords = preg_split('/\s+/', $bit);
        $query = $search->getQuery();
        $grammar = $query->getGrammar();

        // Prepare the LIKE pattern
        $likeBit = '%' . str_replace(' ', '%', $bit) . '%';

        // Subquery for posts
        $postSubquery = Post::whereVisibleTo($search->getActor())
            ->select('posts.discussion_id')
            ->selectRaw('COALESCE(MATCH('.$grammar->wrap('posts.content').') AGAINST (?), 0) + 
                         (CASE WHEN '.$grammar->wrap('posts.content').' LIKE ? THEN 1 ELSE 0 END) as relevance_score', [$bit, $likeBit])
            ->selectRaw('SUBSTRING_INDEX(GROUP_CONCAT('.$grammar->wrap('posts.id').' ORDER BY 
                         (MATCH('.$grammar->wrap('posts.content').') AGAINST (?) + 
                         (CASE WHEN '.$grammar->wrap('posts.content').' LIKE ? THEN 1 ELSE 0 END)) DESC, 
                         '.$grammar->wrap('posts.number').'), \',\', 1) as most_relevant_post_id', [$bit, $likeBit])
            ->selectRaw('0 as is_title_match')
            ->where('posts.type', 'comment')
            ->where(function ($query) use ($grammar, $bit, $likeBit) {
                $query->whereRaw('MATCH('.$grammar->wrap('posts.content').') AGAINST (? IN BOOLEAN MODE)', [$bit])
                      ->orWhere('posts.content', 'LIKE', $likeBit);
            })
            ->groupBy('posts.discussion_id');

        // Subquery for discussions
        $discussionSubquery = Discussion::select('id as discussion_id')
            ->selectRaw('MATCH('.$grammar->wrap('discussions.title').') AGAINST (?) as relevance_score', [$bit])
            ->selectRaw('first_post_id as most_relevant_post_id')
            ->selectRaw('1 as is_title_match')
            ->whereRaw('MATCH('.$grammar->wrap('discussions.title').') AGAINST (? IN BOOLEAN MODE)', [$bit]);

        // Combine both subqueries using UNION
        $unionSubquery = $postSubquery->union($discussionSubquery);

        // Join the subquery results into the main query
        $query
            ->addSelect('discussions.*')
            ->addSelect('search_results.most_relevant_post_id')
            ->addSelect('search_results.relevance_score')
            ->addSelect('search_results.is_title_match')
            ->leftJoin(
                new Expression('('.$unionSubquery->toSql().') search_results'),
                'search_results.discussion_id',
                '=',
                'discussions.id'
            )
            ->whereNotNull('search_results.discussion_id')
            ->groupBy('discussions.id')
            ->addBinding($unionSubquery->getBindings(), 'join');

        // Improved sorting for multi-keyword search
        $search->setDefaultSort(function ($query) use ($keywords) {
            $titleMatchScore = 'search_results.is_title_match * 1';
            $keywordMatchScores = [];
            
            foreach ($keywords as $keyword) {
                $keywordMatchScores[] = "IF(discussions.title LIKE '%" . $keyword . "%', 2, 0)";
                $keywordMatchScores[] = "IF(EXISTS (SELECT 1 FROM posts WHERE posts.discussion_id = discussions.id AND posts.content LIKE '%" . $keyword . "%'), 3, 0)";
            }
            
            $keywordScoreSum = '(' . implode(' + ', $keywordMatchScores) . ')';
            $query->orderByRaw("$titleMatchScore + $keywordScoreSum DESC, search_results.relevance_score DESC");
        });

        return true;
    }
}