On Thu, Jan 23, 2014 at 1:45 AM, Michael Bayer <[email protected]> wrote: > > On Jan 22, 2014, at 7:17 PM, Simon King <[email protected]> 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 >>
That's fantastic, thanks so much. I feel bad that my silly use case has caused so much work for you and grown the docs even more (perhaps you need a separate "Tricks for People who Should Know Better" section) If I understand what you've written correctly, the non-primary-mapper version is the only one that will meet my needs. It seems to be working well, both lazy and eager loading, and the extra properties appearing on the class aren't an issue (I'm marking a lot of them as deferred so as not to load too much from the database). 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 to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
