PostgreSQL：单查询解析一对多关联并删除指定元组

现有表结构 #

CREATE TABLE users (id, ...);
CREATE TABLE groups (id, ...);
CREATE TABLE group_roles (group_id REFERENCES groups.id, role, ...); -- 注：原表中REFERENCES pages.id应为笔误，修正为groups.id
CREATE TABLE user_memberships (
  user_id REFERENCES users.id,
  group_role_id REFERENCES group_roles.id
);

需求 #

通过Sequelize传入多组(user_id, group_id)元组，用单个PostgreSQL查询删除user_memberships中匹配的记录。原SQL仅支持单组元组，无法批量操作。

原无效SQL：

DELETE FROM user_memberships
WHERE (
  (
     user_id = :user_id_n
     AND group_role_id IN (
       SELECT id FROM group_roles WHERE group_id = :group_id_n
     )
  )
);

有效的PostgreSQL批量删除写法 #

方式1：VALUES子句构造批量数据 #

直接构造包含所有待处理元组的临时数据集，关联group_roles找到对应role_id后删除：

DELETE FROM user_memberships um
USING (
  VALUES
    (:user_id_1, :group_id_1),
    (:user_id_2, :group_id_2),
    -- 按需添加更多元组
  ) AS targets(user_id, group_id)
JOIN group_roles gr ON gr.group_id = targets.group_id
WHERE um.user_id = targets.user_id
  AND um.group_role_id = gr.id;

方式2：数组参数动态处理 #

如果元组数量不固定，可通过传入两个等长数组（user_id数组、group_id数组），用unnest拆分后关联：

DELETE FROM user_memberships um
USING (
  SELECT unnest(:user_ids)::INT AS user_id, unnest(:group_ids)::INT AS group_id
) AS targets
JOIN group_roles gr ON gr.group_id = targets.group_id
WHERE um.user_id = targets.user_id
  AND um.group_role_id = gr.id;

注意：两个数组必须长度一致，否则会产生错误的笛卡尔积匹配。

Sequelize实现方案 #

方案1：原生SQL批量执行 #

直接拼接VALUES子句，适合元组数量明确的场景：

const deleteTuples = [
  { userId: 1, groupId: 101 },
  { userId: 2, groupId: 102 },
  // 更多待处理元组
];

// 构造VALUES占位符和参数数组
const valuePlaceholders = deleteTuples.map((_, idx) => `($${idx*2+1}, $${idx*2+2})`).join(',');
const params = deleteTuples.flatMap(t => [t.userId, t.groupId]);

const deleteQuery = `
  DELETE FROM user_memberships um
  USING (VALUES ${valuePlaceholders}) AS targets(user_id, group_id)
  JOIN group_roles gr ON gr.group_id = targets.group_id
  WHERE um.user_id = targets.user_id
    AND um.group_role_id = gr.id
`;

await sequelize.query(deleteQuery, { replacements: params });

方案2：ORM查询构造器实现 #

借助Sequelize的查询语法，先预获取group_id对应的role_id映射，再构造删除条件：

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

const deleteTuples = [
  { userId: 1, groupId: 101 },
  { userId: 2, groupId: 102 },
];

// 预查询group_id到role_id的映射，避免多次数据库请求
const roleGroupMap = await GroupRole.findAll({
  attributes: ['id', 'group_id']
}).then(roles => roles.reduce((map, role) => {
  if (!map[role.group_id]) map[role.group_id] = [];
  map[role.group_id].push(role.id);
  return map;
}, {}));

// 构造批量删除的OR条件
const deleteConditions = deleteTuples.map(tuple => ({
  user_id: tuple.userId,
  group_role_id: { [Op.in]: roleGroupMap[tuple.groupId] || [] }
}));

// 执行批量删除
await UserMembership.destroy({
  where: {
    [Op.or]: deleteConditions
  }
});

内容的提问来源于stack exchange，提问作者Jeff Hemphill