On Jan 22, 2014, at 7:17 PM, Simon King <si...@simonking.org.uk> wrote:
> > I read the bit in the docs about non-primary mappers but was scared > off by the "almost never needed" warnings. Actually, for the purposes > I'm using it for, a completely separate class mapped to the select > would probably be fine. So I’ve added new doc sections: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-join http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper the first one is the one I gave you, and various aspects of that example need things I just committed, though the more version I gave in this email *should* work on 0.8, more or less. The second is showing the classic “secondary mapper” use case and I’ve tried to adjust some of the other language that refers to it. That case is also applicable to 0.8 more or less. I think I’ve identified the difference between “long primaryjoin”, “elaborate secondary”, and “non primary mapper” like this: 1. long primaryjoin works until you have tables mentioned in the clause which are joined to each other, e.g. C and D in these examples. a JOIN between all tables won’t render correctly. 2. elaborate secondary works until you have to refer to conditions directly between A and B, not just within the “secondary” selectable to A and B separately. 3. non primary mapper allows you to set up any series of columns as a target and build a relationship to it, but the current restrictions are either that you have to join to a subquery which is inefficient, or if you join to a join(), you have to disambiguate the names of columns; in both cases you get a bunch of extra columns stuck on your mapping also. > >> Going forward, the a->secondary->b technique, as well as the a-><non primary >> mapper of b, c, d, e...> technique, I am hoping will become more feasible, >> now that the ORM can nest JOINs together without needing to use a SELECT >> subquery. >> >> However, trying out your use case the way I'd like, doesn't work yet, which >> is that I'd like to set up "secondary" just as a join() object. In 0.9.1, >> you still get a SELECT subquery for join() and joinedload(), though you'll >> get the right results; but lazy loads are failing. I can fix both issues >> but that would be in 0.9.2. > >> In 0.9.1, the following setup seems to work so far, but it requires a SELECT >> subquery for the "secondary" table to work fully, which is the join of B and >> C: >> >> from sqlalchemy import * >> from sqlalchemy.orm import * >> from sqlalchemy.ext.declarative import declarative_base >> >> Base = declarative_base() >> >> class A(Base): >> __tablename__ = 'a' >> >> id = Column(Integer, primary_key=True) >> b_id = Column(ForeignKey('b.id')) >> >> >> class B(Base): >> __tablename__ = 'b' >> >> id = Column(Integer, primary_key=True) >> d_id = Column(ForeignKey('d.id')) >> >> class C(Base): >> __tablename__ = 'c' >> >> id = Column(Integer, primary_key=True) >> a_id = Column(ForeignKey('a.id')) >> d_id = Column(ForeignKey('d.id')) >> >> class D(Base): >> __tablename__ = 'd' >> >> id = Column(Integer, primary_key=True) >> >> a = A.__table__ >> b = B.__table__ >> d = D.__table__ >> c = C.__table__ >> >> j = join(b, d, b.c.d_id == d.c.id).join(c, c.c.d_id == d.c.id).alias() >> A.d = relationship("D", >> secondary=j, >> primaryjoin=and_(a.c.b_id == j.c.b_id, a.c.id == j.c.c_a_id), >> secondaryjoin=d.c.id == j.c.b_d_id) >> >> >> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) >> Base.metadata.drop_all(e) >> Base.metadata.create_all(e) >> >> sess = Session(e) >> >> sess.query(A).options(joinedload(A.d)).all() >> sess.query(A).join(A.d).all() > > Mike, thanks as always for the very comprehensive answer. I'm > currently on 0.8 but this seems like a good incentive to upgrade. I'll > try it out tomorrow and will let you know how I get on. > > Thanks again, > > Simon > > -- > 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 tosqlalchemy+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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
