如何用Sequelize实现含子查询内连接的PostgreSQL全文搜索查询

数据表结构 #

table name: user
columns: user_id, name(varchar)

table name: book
columns: book_id, name(varchar)

table name: publisher
columns: publisher_id, user_id(fk to user table), book_id(fk to book table)

需求说明 #

需要实现user表与book表的左外连接查询，满足：

• 通过全文搜索匹配user.name或book.name中包含jack的记录
• 即使user.name不含jack，但关联的book.name含jack（且该user_id存在于publisher表中），也需返回对应用户及关联书籍信息
• 结果按user.name升序排序，分页返回前20条

现有代码问题 #

原Sequelize代码生成的SQL仅筛选了user.name含jack的用户，未将“关联book含jack的用户”纳入筛选范围；同时将book的全文搜索条件放在左外连接的ON子句中，导致无法正确返回符合条件的用户及其所有关联书籍。

修改后的Sequelize代码 #

const { Op, literal } = require('sequelize');

UserModel.findAndCountAll({
  where: {
    [Op.or]: [
      // 用户自身name含jack的全文搜索条件
      literal(`(TO_TSVECTOR('simple', TRANSLATE("user"."name", '/\`~!@#$%^&*()_|+-=?;:''",.<>{}[]\\/]', '                                   ')) @@ 'jack:*')`),
      // 子查询判断用户是否有含jack的关联书籍
      literal(`EXISTS (
        SELECT 1
        FROM "publisher" AS "publishers"
        INNER JOIN "book" AS "book" ON "publishers"."book_id" = "book"."book_id"
        WHERE "publishers"."user_id" = "user"."user_id"
        AND (TO_TSVECTOR('simple', TRANSLATE("book"."name", '/\`~!@#$%^&*()_|+-=?;:''",.<>{}[]\\/]', '                                   ')) @@ 'jack:*')
      )`)
    ]
  },
  order: [['name', 'ASC']],
  group: ['UserModel.user_id'],
  include: [
    {
      model: PublisherModel,
      required: false, // 左外连接publisher表
      include: [
        {
          model: BookModel,
          required: false // 左外连接book表，不额外加where条件
        }
      ]
    }
  ],
  limit: 20,
  offset: 0
});

代码说明 #

1. 主查询WHERE条件：使用Op.or组合两个条件，一是用户自身的全文搜索，二是通过EXISTS子查询判断该用户是否关联了含jack的书籍，确保两种符合条件的用户都被筛选出来
2. 关联表配置：将PublisherModel和BookModel的required都设为false，实现左外连接，这样会返回用户所有关联的publisher和book数据，而不是仅匹配全文搜索的book
3. 保持分页与排序：保留原有的order、group、limit、offset配置，确保结果符合分页排序要求

内容的提问来源于stack exchange，提问作者Linh Nguyen