Good point, but unfortunately, unless I'm missing something, including only
B_base and removing B from the join doesn't seem to work when A is also
derived (using joined-table inheritance) from B_base (which is my actual
situation, despite what the nomenclature here suggest).
On Thursday, February 27, 2014 9:26:31 PM UTC-5, Michael Bayer wrote:
>
>
> On Feb 27, 2014, at 9:23 PM, Seth P <spad...@gmail.com <javascript:>>
> wrote:
>
> Thank you. This was very helpful.
>
> One non-trivial thing that stumped me for a while is that if B is derived
> from a B_base using joined-table inheritance, and the order variable is in
> the base table B_base, then it seems one must include B_base explicitly --
> as highlighted below.
>
>
> OK well maybe you just include B_base in the primaryjoin and not “B” at
> all? you want to include as few tables as possible in these things to
> keep the query plans simpler.
>
>
>
>
> from sqlalchemy import Column, Integer, ForeignKey, create_engine, select,
> join
> from sqlalchemy.orm import create_session, relationship, backref, mapper
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.ext.associationproxy import association_proxy
>
> sqlite = 'sqlite:///test_a_to_b.db'
> e = create_engine(sqlite, echo=True)
> Base = declarative_base(bind=e)
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
> bs = association_proxy("a_to_bs", "b", creator=lambda x: A_to_B(b=x))
>
> class B_base(Base):
> __tablename__ = 'b_base'
> id = Column(Integer, primary_key=True)
> order = Column(Integer)
>
> class B(B_base):
> __tablename__ = 'b'
> id = Column(Integer, ForeignKey(str(B_base.__table__) + ".id"),
> primary_key=True)
>
> class A_to_B(Base):
> __tablename__ = 'a_to_b'
> id = Column(Integer, primary_key=True)
> a_id = Column(Integer, ForeignKey(str(A.__table__) + ".id"),
> nullable=False, index=True)
> a = relationship(A, foreign_keys=[a_id],
> backref=backref("a_to_bs",
> primaryjoin="and_(A_to_B.a_id == A.id, A_to_B.b_id == B.id*, B.id ==
> B_base.id*)", order_by="B.order"))
> b_id = Column(Integer, ForeignKey(str(B.__table__) + ".id"),
> nullable=False)
> b = relationship(B, foreign_keys=[b_id], lazy="subquery")
> def __init__(self, a=None, b=None):
> self.a = a
> self.b = b
>
> if __name__ == '__main__':
> # recreate database
> Base.metadata.drop_all()
> Base.metadata.create_all()
> # populate
> session = create_session(bind=e, autocommit=False)
> a = A(bs=[B(order=10), B(order=2)])
> session.add(a)
> session.commit()
> session.close_all()
> # read
> session = create_session(bind=e, autocommit=False)
> a = session.query(A).one()
> for b in a.bs:
> print b.order
> for a_to_b in a.a_to_bs:
> print a_to_b.b.order
> session.close_all()
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
