多对多关联下无法筛选出不含指定分类ID的用户

问题根源 #

你之前用的查询逻辑（比如直接判断NOT (categories.id = 目标ID)）有漏洞：只要用户关联的分类里有任意一个不是目标ID，这条用户记录就会被匹配出来——哪怕这个用户同时也关联了目标分类，自然会返回所有不符合预期的用户。

正确实现方案 #

1. 原生SQL写法

两种常用思路，性能都不错：

• 子查询排除法：

SELECT u.*
FROM users u
WHERE u.id NOT IN (
    SELECT user_id
    FROM user_category -- 多对多中间表
    WHERE category_id = 你要排除的分类ID
)

• 左连接判空法：

SELECT u.*
FROM users u
LEFT JOIN user_category uc 
    ON u.id = uc.user_id AND uc.category_id = 你要排除的分类ID
WHERE uc.user_id IS NULL

2. JPA/Hibernate 实现

用NOT EXISTS生成精准的子查询逻辑：

@Query("SELECT u FROM User u WHERE NOT EXISTS (" +
       "SELECT uc FROM UserCategory uc WHERE uc.user = u AND uc.category.id = :targetId)")
List<User> findUsersNotInCategory(@Param("targetId") Long targetId);

如果用Spring Data JPA的派生查询，直接用命名规则也能实现：

List<User> findByCategoriesNotContaining(Category targetCategory);

3. Django ORM 实现

两种简洁写法：

• 直接排除关联过目标分类的用户：

target_id = 你要排除的分类ID
User.objects.exclude(categories__id=target_id)

• 用Exists子查询（大数据量下性能更优）：

from django.db.models import Exists, OuterRef

target_id = 你要排除的分类ID
User.objects.annotate(
    has_target=Exists(
        User.categories.through.objects.filter(user_id=OuterRef('id'), category_id=target_id)
    )
).exclude(has_target=True)

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