PostgreSQL：删除team_member_tag中不在指定slug数组的关联记录

需求说明 #

需要删除team_member_tag表中，所有对应tag的slug不在指定数组$tags中的记录。关联逻辑是：通过tag表的slug找到对应的id，再匹配team_member_tag表的tag_id。

示例表结构及数据 #

tag表

team_member_tag表

当$tags = ['first-tag']时，目标是删除tag_id=3743（对应slug为second-tag）的那条记录。

错误尝试的代码 #

之前编写的两种SQL逻辑均无法正常工作，代码如下：

第一种错误实现：

$tags = ['first-tag'];

foreach ($tags as $tag) {
        $stmt = $this->getConnection()->prepare('
            DELETE FROM team_member_tag tmt
            WHERE t.slug = :tag NOT IN
            (SELECT t.id FROM tag t)
         ');

        $stmt->bindValue('tag', $tag);

        $stmt->executeQuery();
    }

第二种错误实现：

foreach ($tags as $tag) {
        $stmt = $this->getConnection()->prepare('
             DELETE FROM team_member_tag tmt
             WHERE NOT EXISTS(SELECT t.id
             FROM tag t
             WHERE t.slug = :tag)
         ');

        $stmt->bindValue('tag', $tag);

        $stmt->executeQuery();
    }

正确解决方案 #

方案一：IN子查询匹配合法tag_id #

先通过$tags数组获取所有合法的tag id，再删除team_member_tag中不在这个集合里的记录，无需循环，一次SQL即可完成：

$tags = ['first-tag'];

$stmt = $this->getConnection()->prepare('
    DELETE FROM team_member_tag tmt
    WHERE tmt.tag_id NOT IN (
        SELECT t.id FROM tag t WHERE t.slug = ANY(:tags)
    )
');

// PostgreSQL支持直接绑定数组参数
$stmt->bindValue('tags', $tags, \Doctrine\DBAL\Types\Types::ARRAY);
$stmt->executeQuery();

方案二：NOT EXISTS关联判断 #

通过关联tag表，判断当前team_member_tag的tag_id对应的tag slug不在指定数组中：

$tags = ['first-tag'];

$stmt = $this->getConnection()->prepare('
    DELETE FROM team_member_tag tmt
    WHERE NOT EXISTS (
        SELECT 1 FROM tag t 
        WHERE t.id = tmt.tag_id 
        AND t.slug = ANY(:tags)
    )
');

$stmt->bindValue('tags', $tags, \Doctrine\DBAL\Types\Types::ARRAY);
$stmt->executeQuery();

错误原因说明 #

1. 语法逻辑混乱：第一个SQL中WHERE t.slug = :tag NOT IN完全不符合关联查询逻辑，未建立team_member_tag和tag的关联关系；
2. 关联缺失：第二个SQL的NOT EXISTS子查询没有关联tmt.tag_id和t.id，导致判断条件与当前要删除的记录无关；
3. 冗余循环：无需逐个处理数组中的tag，PostgreSQL的ANY操作符可直接配合数组参数完成批量匹配。

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