时间戳

首页 > 解决方案 > 如何使用 SQLAlchemy 执行自引用相关子查询？

python - 如何使用 SQLAlchemy 执行自引用相关子查询？

问题描述

我一直避免使用 ORM 来支持手写 SQL。但是，我发现自己现在正在尝试将 SQLAlchemy 用于一个项目，因为它似乎对这个项目有意义。但是，我正在努力弄清楚如何将一些稍微复杂的 SQL 移植到 SQLAlchemy。

我有一个链表实现如下：

class TransactionModel(Base):
    __tablename__ = 'transactionlog'
    chain = Column(String(36), primary_key = True)
    id = Column(String(36), primary_key = True)
    overwrite_id = Column(String(36))
    user_id = Column(Integer, ForeignKey('users.id'))
    timestamp = Column(DateTime, nullable=False, server_default=func.now())
    user = relationship(UserModel, foreign_keys=[user_id])

    __table_args__ = (
        ForeignKeyConstraint(
            ['chain', 'overwrite_id'],
            ['transactionlog.chain', 'transactionlog.id']
        ),
    )

大多数事情看起来很简单：

# Prev Item in list
TransactionModel.query.filter((TransactionModel.chain == current.chain) & (TransactionModel.id == current.overwrite_id)).one()
# Next item in list
TransactionModel.query.filter((TransactionModel.chain == current.chain) & (TransactionModel.overwrite_id == current.id)).one()

但我似乎无法弄清楚如何获得列表中的最后一项。如果我手动编写 SQL，我会这样做：

SELECT * 
FROM transactionlog AS t
WHERE chain = "somevalue"
AND NOT EXISTS (
    SELECT *
    FROM transactionlog AS other
    WHERE other.chain = t.chain
    AND other.overwrite_id = t.id
)

不过，我似乎无法弄清楚如何将其转换为 SQLAlchemy。有人可以指出我正确的方向吗？

标签： pythonsqlalchemy

解决方案

如果我要在 SQLAlchemy 中编写这个，我会使用sqlalchemy.exists()and sqlalchemy.orm.aliased。后者允许您本质上拥有模型的命名副本，因此您可以区分同一个表：

from sqlalchemy import exists
from sqlalchemy.orm import aliased

other = aliased(TransactionModel)

TransactionModel.query.filter(
    TransactionModel.chain == "somevalue",
    ~exists().where(
        (other.overwrite_id == TransactionModel.id) &
        (other.chain == TransactionModel.chain)
    )
)

或等效地（使用and_代替&）

from sqlalchemy import and_, exists
from sqlalchemy.orm import aliased

other = aliased(TransactionModel)

TransactionModel.query.filter(
    TransactionModel.chain == "somevalue",
    ~exists().where(and_(
        other.overwrite_id == TransactionModel.id,
        other.chain == TransactionModel.chain
    ))
)

推荐阅读