Sqlalchemy存在于连接继承和Firebird中

Question

我试图使用sqlachemy连接表继承，但出现了一个奇怪的情况。在

class CommonObject(Base):
    __tablename__ = "objects"
    id = Column("objid", Integer, primary_key=True)
    objname = Column(String(32))
    ...    

class GoodsPlacement(Container, Loadable, Dumpable):
    __tablename__ = "goods_placements"
    id = Column("objid", Integer, ForeignKey("containers.objid"), primary_key=True)
    ...

class Departure(CommonObject):
     __tablename__ = "departures"
    id = Column(Integer, ForeignKey("objects.objid"),  primary_key=True)
    content_id = Column(Integer, ForeignKey("goods_placements.objid"))
    content = relationship("GoodsPlacement",
        primaryjoin="Departure.content_id==GoodsPlacement.id",
        foreign_keys=[content_id],
        lazy='joined',
        backref="departures")
    ...

当我写查询时：

^{pr2}$

它让我产生了这样的东西：

SELECT 
    objects.objid AS objects_objid,
    goods_placements.objid AS goods_placements_objid,
    objects.objname AS objects_objname 
FROM objects
JOIN goods_placements ON objects.objid = goods_placements.objid 
WHERE NOT (EXISTS (
    SELECT 1 
    FROM (
        SELECT
            objects.objid AS objects_objid,
            objects.objname AS objects_objname,
            departures.id AS departures_id,
            departures.content_id AS departures_content_id,
            departures.status_id AS departures_status_id 
        FROM objects
        JOIN departures ON objects.objid = departures.id)
    AS anon_1, objects 
    WHERE anon_1.departures_content_id = objects.objid
        AND anon_1.departures_status_id < :status_id_1)
)

这不起作用，因为exist子句中的对象覆盖了外部对象。 由于我使用的解决方法直接从sqlexpression中存在

session.query(GoodsPlacement).filter(~exists([1],
    and_("departures.status_id<2",
         "departures.content_id=goods_placements.objid"),
    from_obj="departures"))

但它强烈依赖于列和表名。在

如何在exists语句中为object表指定别名？在

Debian wheezy，python-2.7.3rc2，sqlaclhemy 0.7.7-1

Answer 1

声明性系统在设置列时存在一个bug。您给列指定的“objid”名称与“id”属性名不同，是问题的根源。下面的测试用例与您的上述系统类似，并显示了一个解决方法，直到bug被修复：

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class CommonObject(Base):
    __tablename__ = "objects"
    id = Column("objid", Integer, primary_key=True)
    objname = Column(String(32))

class Container(CommonObject):
    __tablename__ = 'containers'
    id = Column("objid", Integer, ForeignKey("objects.objid"),  primary_key=True)

class GoodsPlacement(Container):
    __tablename__ = "goods_placements"
    id = Column("objid", Integer, ForeignKey("containers.objid"), primary_key=True)


class Departure(CommonObject):
    __tablename__ = "departures"
    id = Column(Integer, ForeignKey("objects.objid"),  primary_key=True)
    content_id = Column(Integer, ForeignKey("goods_placements.objid"))
    status_id = Column(Integer)
    content = relationship("GoodsPlacement",
        primaryjoin=lambda:Departure.__table__.c.content_id==GoodsPlacement.__table__.c.objid,
        backref="departures"
        )

session = Session()
print session.query(GoodsPlacement).filter(~GoodsPlacement.departures.any(Departure.status_id < 2))

输出：

^{pr2}$