On 04/20/2017 09:12 PM, Jonathan Vanasco wrote:
i have roughly the following model: Widget > Widget2Foo > Foo > Foo2Bar
> Bar wherein `Foo` has multiple relationships to `Bar` (via a filter )
the setup works for lazyloading and subqueryloading, but I can't do
multiple joinedloads:
eg:
lazyloading and this subquery works:
query = s.query(Widget)\
.options(joinedload('to_foos').joinedload('foo').joinedload('to_bars').joinedload('bar'))\
.options(subqueryload('to_foos.foo.to_bars_special.bar'))
but this won't work, because sqlalchemy has issues with the final segment
query = s.query(Widget)\
.options(joinedload('to_foos').joinedload('foo').joinedload('to_bars').joinedload('bar'))\
.options(joinedload('to_foos').joinedload('foo').joinedload('to_bars_special').joinedload('bar'))
i included the relevant bits below and can make a repeatable if needed .
i've traced the issue to the orm having issues with the the multiple
joins to the same table -- so I figure i'm either pushing the limits or
implemented that last relationship wrong and this will be obvious to
someone with more experience.
---
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
to_bars = relationship("Foo2Bar",
primaryjoin="""Foo.id==Foo2Bar.foo_id""",
)
to_bars_special = relationship("Foo2Bar",
primaryjoin="""and_(Foo.id==Foo2Bar.foo_id,
Foo2Bar.bar_id==Bar.id,
Bar.is_special==True,
)""",
oh, wait. how is that supposed to work w/ "Bar"? that will definitely
fail. the biggest reason would be that "Bar" is not part of the FROMs
it expects so you are likely getting something like "FROM foo JOIN
foo2bar ON <onclause>, bar".
Take a look at very simple query(Foo).join(Foo.to_bars_special) to see
if it's relying on that. if you see a comma in the FROM clause, you're
out.
to get that into a joinedload you'd need to encapsulate that join a
little better, this is discussed at
http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#relationship-to-non-primary-mapper
"when we seek to join from A to B, making use of any number of C, D,
etc. in between, however there are also join conditions between A and B
directly."
seems like it describes this case !
)
class Bar(Base):
__tablename__ = 'bar'
id = Column(Integer, primary_key=True)
is_special = Column(Boolean, nullable=True)
class Foo2Bar(Base):
__tablename__ = 'foo2bar'
__primarykey__ = ['foo_id', 'bar_id']
foo_id = Column(Integer, ForeignKey("Foo.id"), primary_key=True)
bar_id = Column(Integer, ForeignKey("Bar.id"), primary_key=True)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and
Verifiable Example. See http://stackoverflow.com/help/mcve for a full
description.
---
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
