PostgreSQL双表查询多item_sid对应content匹配的SQL及SQLAlchemy写法求助

你原来的查询逻辑存在本质错误：WHERE子句是对关联后生成的单条记录做过滤，不可能要求同一条Records记录同时匹配两个不同的item_sid取值，因此你的查询会永远返回空结果。另外注意核对实际表名拼写，你示例中的表名为Records，原SQL中写的record单复数不匹配也可能导致报错。

两种常用实现方案：

方案1：分组聚合校验（推荐，适合条件数量动态变化的场景） #

SELECT r.sid, r.unit_sid
FROM "Rows" r
INNER JOIN "Records" rec ON r.sid = rec.row_sid
WHERE 
    (rec.item_sid = 'ITEM_UUID1' AND rec.content = 'Description 1')
    OR 
    (rec.item_sid = 'ITEM_UUID2' AND rec.content = 'Description 1')
GROUP BY r.sid, r.unit_sid
-- 数值等于你需要同时满足的条件总数，新增条件只需同步修改该数值
HAVING COUNT(DISTINCT rec.item_sid) = 2;

逻辑说明：先过滤出满足任意一个要求的关联记录，再按Rows表主键分组，统计每个Rows关联的匹配记录中不同item_sid的数量，刚好等于条件总数就说明该Rows记录同时满足所有要求。

方案2：多表关联（适合固定少量条件的场景） #

SELECT DISTINCT r.sid, r.unit_sid
FROM "Rows" r
-- 每个条件对应关联一次Records表
INNER JOIN "Records" rec1 
    ON r.sid = rec1.row_sid 
    AND rec1.item_sid = 'ITEM_UUID1' 
    AND rec1.content = 'Description 1'
INNER JOIN "Records" rec2 
    ON r.sid = rec2.row_sid 
    AND rec2.item_sid = 'ITEM_UUID2' 
    AND rec2.content = 'Description 1';

首先假设你已经定义了如下ORM模型：

from sqlalchemy import Column, String, ForeignKey
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()

class Row(Base):
    __tablename__ = 'Rows'
    sid = Column(String, primary_key=True)
    unit_sid = Column(String)
    records = relationship("Record", back_populates="row")

class Record(Base):
    __tablename__ = 'Records'
    id = Column(String, primary_key=True)
    row_sid = Column(String, ForeignKey('Rows.sid'))
    item_sid = Column(String)
    content = Column(String)
    row = relationship("Row", back_populates="records")

对应分组聚合方案的写法 #

from sqlalchemy import func, distinct

# 条件可动态扩展
conditions = [
    (Record.item_sid == '877aeeb4-c68e-4942-b259-288e7aa3c04b', Record.content.like('%TEXT%')),
    (Record.item_sid == 'cc22f239-db6c-4041-92c6-8705cb621525', Record.content.like('%TEXT2%')),
]

# 构造OR过滤逻辑
filter_clause = [item_cond & content_cond for item_cond, content_cond in conditions]

query = session.query(Row)\
    .join(Record)\
    .filter(func.or_(*filter_clause))\
    .group_by(Row.sid)\
    .having(func.count(distinct(Record.item_sid)) == len(conditions))

# 获取结果
rows = query.all()

对应多表关联方案的写法 #

from sqlalchemy.orm import aliased

# 为每次关联创建别名
rec1 = aliased(Record)
rec2 = aliased(Record)

query = session.query(Row).distinct()\
    .join(rec1, 
          (Row.sid == rec1.row_sid) 
          & (rec1.item_sid == '877aeeb4-c68e-4942-b259-288e7aa3c04b') 
          & (rec1.content.like('%TEXT%')))\
    .join(rec2, 
          (Row.sid == rec2.row_sid) 
          & (rec2.item_sid == 'cc22f239-db6c-4041-92c6-8705cb621525') 
          & (rec2.content.like('%TEXT2%')))

# 获取结果
rows = query.all()

提示：PostgreSQL中大小写敏感的标识符需要用双引号包裹，若你的表名、字段名是小写存储则可以去掉SQL语句中的双引号。

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