Sqlalchemy exists with joined inheritance and Firebird

Question

I tried to use sqlaclhemy joined table inheritance and had a strange occurrence.

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")
    ...

When I write query:

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

it generates me something like this:

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)
)

And this doesn't work because objects in exist clause overrides outer objects. As workaround I used exists from sqlexpression directly,

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

but it strongly depends from column and table names.

How I can specify alias for object table in exists statement?

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

Answer 1

there's a bug involving the declarative system in how it sets up columns. The "objid" name you're giving the columns, distinct from the "id" attribute name, is the source of the issue here. The below test case approximates your above system and shows a workaround until the bug is fixed:

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))

output:

SELECT objects.objid AS objects_objid, containers.objid AS containers_objid, goods_placements.objid AS goods_placements_objid, objects.objname AS objects_objname 
FROM objects JOIN containers ON objects.objid = containers.objid JOIN goods_placements ON containers.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 
WHERE anon_1.departures_content_id = goods_placements.objid AND anon_1.departures_status_id < :status_id_1))