> On Oct 27, 2014, at 8:56 PM, Jonathan Vanasco <[email protected]> wrote: > > I've been staring at this a few hours, and can't figure out how to adapt a > join/query into a relationship. The archives and docs don't seem to help > much. > > It seems reminiscent to an earlier question by Simon King ( > https://groups.google.com/d/topic/sqlalchemy/OtI4Z8v7gRs/discussion ) and > some of the more advanced relationship docs -- but I fear I'm overthinking > this. > > At the most basic form, I have two tables that can serve as > intermediary/association tables to the same tables: > > class Foo(Base): > __tablename__ = 'foo' > id = Column(Integer, primary_key=True) > > class Bar(Base): > __tablename__ = 'bar' > id = Column(Integer, primary_key=True) > > class FooBarA(Base): > __tablename__ = 'foo_bar_a' > id = Column(Integer, primary_key=True) > foo_id = Column(Integer, ForeignKey("foo.id"), nullable=False) > bar_id = Column(Integer, ForeignKey("bar.id"), nullable= False) > > class FooBarB(Base): > __tablename__ = 'foo_bar_b' > id = Column(Integer, primary_key=True) > foo_id = Column(Integer, ForeignKey("foo.id"), nullable=False) > bar_id = Column(Integer, ForeignKey("bar.id"), nullable= False) > > i am trying to make the collection of similar relationships available to each > association class > > I was hoping something simple would work: > > FooBarA.foo_bar_bs = relationship("FooBarB", > primaryjoin="and_(FooBarA.foo_id==FooBarB.foo_id, > FooBarA.bar_id==FooBarB.bar_id)") > FooBarB.foo_bar_as = relationship("FooBarA", > primaryjoin="and_(FooBarA.foo_id==FooBarB.foo_id, > FooBarA.bar_id==FooBarB.bar_id)") > > session.query(FooBarB).options(joinedload('foo_bar_a')) > session.query(FooBarB).options(subqueryload('foo_bar_a')) > > But SqlAlchemy wants me to stick to the pattern where primary keys are > involved. I totally understand - that stuff is needed for all the advanced > collections operations. > > It looks like I could achieve what I want with some of the examples in the > advanced relationships docs, but it looks like it wouldn't be as performance > friendly as direct queries, since I'd have to map everything onto the > original underlying primary keys. > > Does anyone know of a more low-impact approach to handling this sort of orm > relationship? I think I might just use a @property on the class
OK well I don’t have a lot of time to look deeply at the moment, but typically one of the two “advanced” patterns http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins <http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins> or http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper <http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper> can pretty much do anything, see if you can work with one of those. > > -- > 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] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at http://groups.google.com/group/sqlalchemy > <http://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. -- 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/d/optout.
