多组一对多关联条件SQL查询返回空结果的解决方法

原查询返回空的核心原因：
你在WHERE子句里要求同一行Attributes的name同时等于objectclass和uid、value同时等于posixaccount和username3——这在单条属性记录里根本不可能实现，自然查不到结果。

下面是几种可行的修改方案，适配你「条件数量不固定」的需求：

方案1：多次关联Attributes表（适合条件较少的场景） #

给每个属性条件单独关联一次Attributes表，每个关联对应一组(name, value)条件：

SELECT 
    "Directory"."parentId", "Directory"."objectClass", "Directory"."whenCreated", 
    "Directory"."whenChanged", "Directory".id, "Directory".name, "Directory".depth, 
    "Users_1"."sAMAccountName", "Users_1"."userPrincipalName", "Users_1"."displayName", 
    "Users_1"."directoryId", "Users_1".id AS id_1, "Users_1".mail, "Users_1".password 
FROM "Directory" 
LEFT OUTER JOIN "Users" ON "Directory".id = "Users"."directoryId" 
-- 第一个属性条件：objectClass=posixaccount
JOIN "Attributes" AS attr1 ON "Directory".id = attr1."directoryId" 
    AND lower(attr1.name) = 'objectclass' AND lower(attr1.value) = 'posixaccount'
-- 第二个属性条件：uid=username3
JOIN "Attributes" AS attr2 ON "Directory".id = attr2."directoryId" 
    AND lower(attr2.name) = 'uid' AND lower(attr2.value) = 'username3'
JOIN "Paths" ON "Directory".id = "Paths".endpoint_id 
LEFT OUTER JOIN "Users" AS "Users_1" ON "Directory".id = "Users_1"."directoryId"

如果条件数量动态变化，在SQLAlchemy里可以循环添加JOIN语句，每次用不同的别名关联Attributes表。

方案2：使用EXISTS子查询（推荐，适配动态条件） #

每个属性条件对应一个EXISTS子查询，检查当前Directory是否存在符合条件的属性记录，这种方式更灵活，适合条件数量不固定的场景：

SELECT 
    "Directory"."parentId", "Directory"."objectClass", "Directory"."whenCreated", 
    "Directory"."whenChanged", "Directory".id, "Directory".name, "Directory".depth, 
    "Users_1"."sAMAccountName", "Users_1"."userPrincipalName", "Users_1"."displayName", 
    "Users_1"."directoryId", "Users_1".id AS id_1, "Users_1".mail, "Users_1".password 
FROM "Directory" 
LEFT OUTER JOIN "Users" ON "Directory".id = "Users"."directoryId" 
JOIN "Paths" ON "Directory".id = "Paths".endpoint_id 
LEFT OUTER JOIN "Users" AS "Users_1" ON "Directory".id = "Users_1"."directoryId"
WHERE
    -- 检查存在objectClass=posixaccount的属性
    EXISTS (
        SELECT 1 FROM "Attributes" 
        WHERE "Attributes"."directoryId" = "Directory".id 
        AND lower("Attributes".name) = 'objectclass' 
        AND lower("Attributes".value) = 'posixaccount'
    )
    -- 检查存在uid=username3的属性
    AND EXISTS (
        SELECT 1 FROM "Attributes" 
        WHERE "Attributes"."directoryId" = "Directory".id 
        AND lower("Attributes".name) = 'uid' 
        AND lower("Attributes".value) = 'username3'
    )

在SQLAlchemy中，你可以把条件封装成列表，然后循环生成EXISTS子查询添加到WHERE子句里，示例代码大致如下：

from sqlalchemy import exists, select, func

# 假设你的条件是动态生成的列表，格式为[(name, value), ...]
conditions = [('objectclass', 'posixaccount'), ('uid', 'username3')]

# 基础查询（替换成你实际的模型关联逻辑）
query = session.query(Directory, Users_1).join(Paths)

# 动态添加EXISTS条件
for name, value in conditions:
    subquery = select(1).where(
        Attributes.directoryId == Directory.id,
        func.lower(Attributes.name) == name.lower(),
        func.lower(Attributes.value) == value.lower()
    )
    query = query.filter(exists(subquery))

# 执行查询
results = query.all()

方案3：GROUP BY + HAVING（适合统计类场景） #

如果需要统计满足所有条件的属性数量，可以用GROUP BY结合HAVING子句：

SELECT 
    "Directory"."parentId", "Directory"."objectClass", "Directory"."whenCreated", 
    "Directory"."whenChanged", "Directory".id, "Directory".name, "Directory".depth, 
    "Users_1"."sAMAccountName", "Users_1"."userPrincipalName", "Users_1"."displayName", 
    "Users_1"."directoryId", "Users_1".id AS id_1, "Users_1".mail, "Users_1".password 
FROM "Directory" 
LEFT OUTER JOIN "Users" ON "Directory".id = "Users"."directoryId" 
JOIN "Attributes" ON "Directory".id = "Attributes"."directoryId"
JOIN "Paths" ON "Directory".id = "Paths".endpoint_id 
LEFT OUTER JOIN "Users" AS "Users_1" ON "Directory".id = "Users_1"."directoryId"
WHERE
    (lower("Attributes".name) = 'objectclass' AND lower("Attributes".value) = 'posixaccount')
    OR (lower("Attributes".name) = 'uid' AND lower("Attributes".value) = 'username3')
GROUP BY "Directory".id, "Users_1".id  -- 需要包含所有SELECT里的非聚合字段
HAVING COUNT(DISTINCT "Attributes".name) = 2  -- 条件数量是2，动态修改这个值

这种方式需要注意GROUP BY的字段必须包含所有SELECT中的非聚合字段，当条件数量变化时，修改HAVING里的COUNT值即可。

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