如何用JOIN和多WHERE语句查询SQLite双表？含SQLAlchemy方案

一、SQLite原生查询语句 #

通过左连接关联接收记录与对应发送记录，再匹配消息表内容，即可实现需求：

SELECT
    m.message,
    r.name,
    s.name AS sender_name
FROM
    Action r
LEFT JOIN
    Action s ON r.message_id = s.message_id AND s.action = 'send'
JOIN
    Message m ON r.message_id = m.id
WHERE
    r.action = 'receive' AND r.name = 'john';

逻辑说明： #

• 用Action r别名筛选出所有John接收的记录（action='receive'且name='john'）
• 左连接Action s别名，仅匹配同message_id下的发送操作记录，确保无对应发送者时sender_name返回NULL
• 内连接Message表，获取每条接收记录对应的消息文本

二、SQLAlchemy实现方式 #

假设已定义如下ORM模型：

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, aliased
from sqlalchemy import select, and_

Base = declarative_base()

class Message(Base):
    __tablename__ = 'Message'
    id = Column(Integer, primary_key=True)
    message = Column(String)
    actions = relationship("Action", back_populates="message")

class Action(Base):
    __tablename__ = 'Action'
    id = Column(Integer, primary_key=True)
    message_id = Column(Integer, ForeignKey('Message.id'))
    action = Column(String)
    name = Column(String)
    message = relationship("Message", back_populates="actions")

方式1：基础外连接查询 #

使用表别名实现与原生SQL一致的逻辑：

# 初始化会话
session = sessionmaker(bind=your_database_engine)()

# 定义接收/发送记录的别名
receive_action = aliased(Action)
send_action = aliased(Action)

# 构建查询语句
query_stmt = (
    select(
        Message.message,
        receive_action.name,
        send_action.name.label('sender_name')
    )
    .select_from(receive_action)
    .outerjoin(
        send_action,
        and_(
            receive_action.message_id == send_action.message_id,
            send_action.action == 'send'
        )
    )
    .join(Message, receive_action.message_id == Message.id)
    .where(
        receive_action.action == 'receive',
        receive_action.name == 'john'
    )
)

# 执行查询并获取结果
results = session.execute(query_stmt).all()

方式2：模型关联简化查询 #

在Action模型中添加发送者关联关系，后续查询可直接引用：

# 更新Action模型，添加发送者关联
class Action(Base):
    __tablename__ = 'Action'
    id = Column(Integer, primary_key=True)
    message_id = Column(Integer, ForeignKey('Message.id'))
    action = Column(String)
    name = Column(String)
    message = relationship("Message", back_populates="actions")
    # 定义发送者关联（仅用于查询）
    sender = relationship(
        "Action",
        primaryjoin="and_(Action.message_id == foreign(Action.message_id), Action.action == 'send')",
        uselist=False,
        viewonly=True
    )

之后的查询会更简洁：

query_stmt = (
    select(
        Message.message,
        Action.name,
        Action.sender.name.label('sender_name')
    )
    .join(Action.message)
    .where(
        Action.action == 'receive',
        Action.name == 'john'
    )
)

results = session.execute(query_stmt).all()

结果处理 #

查询结果可直接转换为字典格式使用：

for row in results:
    print({
        "message": row.message,
        "name": row.name,
        "sender_name": row.sender_name
    })

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