Re: [sqlalchemy] issue with joineload and multiple relationships to the same table

2017-04-21 Thread Jonathan Vanasco 
Thanks. That's exactly it.  It makes perfect sense why it just happens to 
work when I join one bar onto this, but not two.

FYI, I'm not seeing a comma on the simple join - but it's still a bad query 
that illustrates the problem as Bar doesn't get joined in:

   SELECT foo.id AS foo_id  FROM foo JOIN foo2bar ON foo.id = 
foo2bar.foo_id AND foo2bar.bar_id = bar.id AND bar.is_special = true LIMIT 
%(param_1)s

-- 
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.

Re: [sqlalchemy] issue with joineload and multiple relationships to the same table

2017-04-21 Thread mike bayer 



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 , 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 
.
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.


--
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.

[sqlalchemy] issue with joineload and multiple relationships to the same table

2017-04-20 Thread Jonathan Vanasco 
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,
  )""",
   )

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.
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.