selectin / omit_join with two-part primaryjoin

[Mike Waldman]

Hello,

Is anything special required to get omit_join to work on a relationship with a two-part primary join?

With lazy='selectin' and omit_join=True, I am finding that the second part of the primaryjoin is not getting applied, even in a toy example. That is, children with a status of -1 are coming back with the Parent object.

class Child(Base):

  __tablename__ = 'child'

  id = sa.Column(sa.Integer, primary_key=True)

  parent_id = sa.Column(sa.Integer, sa.ForeignKey('parent.id'))

  status = sa.Column(sa.Integer)

class Parent(Base):

  __tablename__ = 'parent'

  id = sa.Column(sa.Integer, primary_key=True)

  children = orm.relationship('Child',

                              lazy='selectin',

                              omit_join=True,

                              primaryjoin=lambda: sa.and_(

                                  Child.parent_id == Parent.id,

                                  Child.status != -1,

                              ))

In the toy example, setting lazy='select' solves this. However, I have a production example where omit_join=True causes only the first join-test to occur, for both lazy='select' and lazy='selectin'. But I figured I'd get to the bottom of the toy example first.

Thanks!

[Mike Bayer]

On Wed, Oct 30, 2019, at 1:58 PM, Mike Waldman wrote:

Hello,

Is anything special required to get omit_join to work on a relationship with a two-part primary join?

the omit_join flag is only intended to *disable* the automatic "omit_join" optimization, if it is occurring and is getting in the way of the query generating correctly.        setting it to True is not supported and it looks like the documentation should be updated to indicate this.   the condition under which omit_join is supported is detected automatically.

for your primaryjoin condition, the SQL optimization applied by "omit_join" can work in theory, however if it's not working for you, that's because the logic is making assumptions about the join condition that aren't currently supported by your custom join condition.

--

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1b4f96d4-cc4f-4f7a-8ae0-230967ca7db1%40googlegroups.com.