rails: 使用 fts5/fts 对 sqlite 进行全文搜索 - rails 6.0

在 rails6 下完成 sqlie3 的 fts 配置

原来的

确认 `sqlite3` 版本

# sqlie3 版本
sqlite3 --version
# rails console 确认
irb(main):001:0> ActiveRecord::Base.connection.select_value("SELECT sqlite_version()")

创建 FTS 虚拟表

由于 SQLite 的 FTS 是虚拟表，不能通过 ActiveRecord 的常规迁移直接生成，我们需要使用 execute 手动创建。

创建一个新的迁移文件：rails db:migrate

rails generate migration CreatePostsFTS

class CreatePostsFts < ActiveRecord::Migration[6.0]
  def up
    # 创建 FTS 虚拟表
    execute <<-SQL
      CREATE VIRTUAL TABLE posts_fts USING fts5(title);
    SQL

    # 将现有的 posts 数据同步到 FTS 表
    execute <<-SQL
      INSERT INTO posts_fts(rowid, title)
      SELECT id, title FROM posts;
    SQL
  end

  def down
    # 删除 FTS 表
    execute <<-SQL
      DROP TABLE IF EXISTS posts_fts;
    SQL
  end
end

同步数据的方法

class Post < ApplicationRecord
  after_save :update_fts
  after_destroy :remove_from_fts
  
  # 设置关联
  has_one :post_fts, foreign_key: "rowid"

  private

  def update_fts
    ActiveRecord::Base.connection.execute(<<-SQL)
      INSERT INTO posts_fts(rowid, title)
      VALUES(#{id}, #{ActiveRecord::Base.connection.quote(title)})
      ON CONFLICT(rowid) DO UPDATE SET title=excluded.title;
    SQL
  end

  def remove_from_fts
    ActiveRecord::Base.connection.execute(<<-SQL)
      DELETE FROM posts_fts WHERE rowid=#{id};
    SQL
  end
end

migrate

rails db:migrate

修改查询方法

在 Rails 控制器或模型中使用 FTS 查询，将 MATCH 语法嵌入 SQL 查询：

class PostsController < ApplicationController
  def index
    keyword = params[:q] || ""
    @posts = Post
               .joins("JOIN posts_fts ON posts.id = posts_fts.rowid")
               .where("posts_fts MATCH ?", keyword)
               .order(douban_rate: :desc)
               .limit(params[:limit] || 10)
               .offset(params[:offset] || 0)
    render json: @posts
  end
end

测试你的实现

# 查询
Post.joins("JOIN posts_fts ON posts.id = posts_fts.rowid")
    .where("posts_fts MATCH ?", "设计")
    .order(douban_rate: :desc)
    .limit(10)

# 测试数据同步：
Post.create(title: "新的设计标题")
Post.last.update(title: "更新后的设计标题")
Post.last.destroy

改进 search 方法

class Post 
  def self.search(keywords)
    if keywords.present?
      joins("JOIN posts_fts ON posts.id = posts_fts.rowid")
        .where("posts_fts MATCH ?", keywords)
    else
      all
    end
  end
end

增强功能（可选）

支持多关键词查询：如果希望支持多个关键词搜索，可以对传入的 keywords 参数进行预处理，比如将空格替换为 AND：

def self.search(keywords)
  if keywords.present?
    processed_keywords = keywords.split.map { |word| "\"#{word}\"" }.join(" ")
    joins("JOIN posts_fts ON posts.id = posts_fts.rowid")
      .where("posts_fts MATCH ?", processed_keywords)
  else
    all
  end
end