在ORM查询中使用原始SQL
是否可以使用您自己的原始SQL进行编辑或覆盖ORM生成的SQL?还是期望ORM具有足够的灵活性来构建几乎我可以想象的任何查询?
具体来说,这是我要尝试执行的查询,尽管我看不到任何明显的构建路径,但是通过ORM进行构建并不是很困难。这是模型:
class AllocationStatus(Base):
STATUS_RESERVED = 1
STATUS_RELEASED = 2
STATUS_CHOICES = (
(STATUS_RESERVED, "Reserved"),
(STATUS_RELEASED, "Released"),
)
__tablename__ = 'allocation_status'
id = Column(Integer, primary_key=True)
allocation_id = Column(Integer, ForeignKey('allocation.id'))
allocation = relationship('Allocation')
status = Column(Integer())
我的想法是,对于给定的外键ID allocation_id
,我想知道中的最新记录allocation_status
。
为了在原始SQL中实现此目的,以下查询是我的目标:
SELECT allocation_status.*
FROM allocation_status
LEFT JOIN allocation_status allocation_status2
ON allocation_status.allocation_id = allocation_status2.allocation_id
AND allocation_status.id < allocation_status2.id
WHERE allocation_status2.id IS NULL;
-
您可以通过以下方式构建它:首先对模型进行别名,然后将该别名用作外部联接的第二个表。以下假设您已经有一个绑定到工作引擎的会话:
from sqlalchemy.orm import aliased from sqlalchemy import and_ allocation_status2 = aliased(AllocationStatus) session.query(AllocationStatus).\ outerjoin(allocation_status2, and_(AllocationStatus.allocation_id == allocation_status2.allocation_id, AllocationStatus.id < allocation_status2.id)).\ filter(allocation_status2.id.is_(None)).all()
我希望这有帮助。