Sequelize findAndCountAll：hasMany关联下分页与搜索冲突问题

问题分析 #

当前使用findAndCountAll时遇到两难：

• 设置subQuery: true时，父级where中引用关联表别名$tags1.tag.name$会报错，因为子查询中的关联表在父查询作用域不可见；
• 设置subQuery: false时，由于Texperts与TexpertsTags是hasMany关联，左连接会生成重复的Texperts记录，导致分页偏移量计算错误、总数统计重复，分页功能失效。

解决方案：子查询预筛选主表ID #

核心思路是先通过子查询筛选出所有符合搜索条件的Texperts主键ID，再让主查询仅基于这些ID进行查询、关联和分页，从根源避免重复记录干扰分页和计数。

修改后的完整代码 #

// 1. 子查询：获取符合搜索条件的Texperts ID列表
const expertIdsSubquery = Texperts.findAll({
  attributes: ['id'],
  where: {
    ...(searchKey && {
      [Op.or]: [
        { '$user.full_name$': { [Op.like]: `%${searchKey}%` } },
      ],
    }),
  },
  include: [
    {
      model: User,
      where: { userId: { [Op.not]: user_id } },
      required: true,
    },
    // 仅当有搜索关键词时，关联标签表过滤匹配的专家
    ...(searchKey ? [{
      model: TexpertTags,
      as: 'tags1',
      required: true,
      include: [{
        model: TexpertTagsData,
        as: 'tag',
        where: { name: { [Op.like]: `%${searchKey}%` } },
        required: true,
      }],
    }] : []),
  ],
  distinct: true,
});

// 2. 主查询：基于预筛选的ID执行查询、关联和分页
const result = await Texperts.findAndCountAll({
  subQuery: false,
  where: {
    id: { [Op.in]: expertIdsSubquery },
    ...(tag_id && { '$tags1.tag_id$': tag_id }), // 若有标签ID过滤，移至此处或子查询
  },
  attributes: {
    exclude: ["createdAt", "updatedAt", "deletedAt"],
    include: [
      [
        Sequelize.literal(`(
          SELECT count(*) 
          FROM texpert_service_bookings AS bookings
          INNER JOIN texpert_services AS services ON services.id = bookings.service_id 
          INNER JOIN texperts as t ON t.id=services.texpert_id AND t.id=Texperts.id
        )`),
        "totalBookings",
      ],
      [
        Sequelize.literal(`
        CASE
          WHEN Texperts.user_id = ? THEN NULL
          ELSE EXISTS (
            SELECT 1
            FROM connections  
            WHERE (connections.accepted = ?) AND 
                  ((connections.from_userId = ? AND connections.to_userId = Texperts.user_id) OR 
                   (connections.to_userId = ? AND connections.from_userId = Texperts.user_id))
          )
        END
      `),
        "is_connected",
      ],
    ],
  },
  bind: [user_id, true, user_id, user_id], // 参数绑定避免SQL注入
  include: [
    {
      model: User,
      where: { userId: { [Op.not]: user_id } },
      attributes: USER_DEFAULT_ATTRIBUTES,
    },
    {
      model: TexpertTags,
      as: "tags1",
      attributes: ["tag_id"],
      where: { ...(tag_id && { tag_id }) },
      include: [{
        model: TexpertTagsData,
        as: "tag",
        attributes: ["id", "name"],
      }],
    },
    {
      model: TexpertTags,
      as: "tags",
      attributes: ["id"],
      include: [{
        model: TexpertTagsData,
        attributes: ["id", "name"],
      }],
    },
    {
      model: TexpertServices,
      required: true,
      attributes: { exclude: ["createdAt", "updatedAt", "deletedAt"] },
    },
  ],
  order: [
    [Sequelize.literal("is_connected"), "DESC"],
    [Sequelize.literal("totalBookings"), "DESC"]
  ],
  distinct: true,
  // 分页配置
  ...(page && size && {
    offset: (page - 1) * size,
    limit: size,
  }),
});

关键优化点 #

1. 子查询预过滤：通过子查询提前锁定符合条件的主表ID，主查询仅处理这些唯一ID，避免关联导致的重复记录；
2. 修正逻辑括号：修复原connections查询中的逻辑括号错误，确保accepted=true同时满足双向连接的任一条件；
3. SQL注入防护：将硬编码的变量替换为参数绑定bind，避免安全风险；
4. 条件关联加载：仅当有搜索关键词时才加载标签关联进行过滤，减少不必要的查询开销。

内容的提问来源于stack exchange，提问作者Akshay Kumar K