Backref primary join condition stopped working in 0.9.4

[Thorsten von Stein]

For several years, I have been using a pattern for making a many-to-one relationship from cls to remoteCls with a one-to-many backref with a join condition cls.foreignKey == remoteCls.id, where
cls has a deletion flag _del which should exclude cls instances with del != 0 from the backref collection.

Since the condition involving _del is only relevant in the one-to-many direction, I defined separate primaryjoin conditions which included this condition only for the backref.

br = backref(
    backref,
    collection_class=list,
    primaryjoin=and_(remoteCls.id==remote(getattr(cls, foreignKey)), cls._del==0))
   
rel = relationship(
    remoteCls,
    remote_side=remoteCls.id,
    primaryjoin=getattr(cls, foreignKey)==remoteCls.id,
    backref=br)
   
I have used this pattern successfully for years until I recently upgraded SqlAlchemy to the latest version and found that the join condition on the backref seems to be ignored and queries include instances that are flagged as deleted via the _del column. I tested several intermediate SqlAlchemy version and found that the first one which breaks the pattern is 0.9.4.

Subsequently I found that removing the primary join condition on the backref and including the _del != 0 condition in the forward primary join condition seems to restore the intended behavior, but now many queries involving large collections are dramatically slowed to make this solution unworkable.

I reviewed the desciptions of changes, but they did not clarify for me why the pattern above does not work any more. Is there a flaw in my code that I am missing?

[Mike Bayer]

On 03/16/2016 02:37 PM, Thorsten von Stein wrote:
> For several years, I have been using a pattern for making a many-to-one

> relationship from *cls* to *remoteCls* with a one-to-many backref with a

> join condition cls.foreignKey == remoteCls.id, where

> *cls* has a deletion flag _del which should exclude *cls* instances with

There are no changes that should affect the behavior of relationship in
this way. If anything, I'd wonder if the "0" value here is actually a
boolean and is interacting with some backend-specific typing behavior,
but there's not enough detail here to know.

Below is a complete test of your concept which succeeds. Please alter
this test appropriately to illustrate your failure condition occurring,
thanks!

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)


class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey('a.id'))
_del = Column("del", Integer, default=0)


def make_rel(cls, remoteCls, foreignKey, backref_name):
br = backref(
backref_name,
collection_class=list,
primaryjoin=and_(
remoteCls.id == remote(getattr(cls, foreignKey)),
cls._del == 0)
)

rel = relationship(
remoteCls,
remote_side=remoteCls.id,
primaryjoin=getattr(cls, foreignKey) == remoteCls.id,
backref=br)

return rel

B.a = make_rel(B, A, "a_id", "bs")

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

b1, b2, b3 = B(), B(), B()
a1 = A(bs=[b1, b2, b3])

s.add(a1)
s.commit()

b2._del = 1
s.commit()

assert a1.bs == [b1, b3]






>
> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Thorsten von Stein]

Mike,

Thank you very much for your quick reply and for creating the test case. Upon closer inspection, I noticed that the errors in my system resulted from a special case of a self-referential relationship as in the following modification of your test case, in which additionally the post_update flag is required. In that case, the assertion fails. However, with an additional remote annotation to make the _del==0 condition unambiguous, the example works again. Now the only question that remains is why the original version was working fine up to 0.9.3.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class C(Base):
     __tablename__ = 'c'
     id = Column(Integer, primary_key=True)
     parent_id = Column(ForeignKey('c.id'))

     _del = Column("del", Integer, default=0)

def make_rel(cls, remoteCls, foreignKey, backref_name):
     br = backref(
         backref_name,
         collection_class=list,
         primaryjoin=and_(
             remoteCls.id == remote(getattr(cls, foreignKey)),

             cls._del == 0 # works with: remote(cls._del) == 0
         )

     )

     rel = relationship(
         remoteCls,
         remote_side=remoteCls.id,
         primaryjoin=getattr(cls, foreignKey) == remoteCls.id,

         post_update = True,
         backref=br)
     return rel

C.parent = make_rel(C, C, "parent_id", "children")

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

c1, c2, c3 = C(), C(), C()
c0 = C(children=[c1, c2, c3])

s.add(c0)
s.commit()

c2._del = 1
s.commit()


assert c0.children == [c1, c3]

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/43rA8XsVuBQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Mike Bayer]

On 03/16/2016 06:10 PM, Thorsten von Stein wrote:
> Mike,
>
> Thank you very much for your quick reply and for creating the test case.
> Upon closer inspection, I noticed that the errors in my system resulted
> from a special case of a self-referential relationship as in the
> following modification of your test case, in which additionally the
> post_update flag is required. In that case, the assertion fails.
> However, with an additional remote annotation to make the _del==0
> condition unambiguous, the example works again.

Right you'd need that remote() there for that mapping to be correct. The
post_update part does not matter.

Now the only question
> that remains is why the original version was working fine up to 0.9.3.

Looking at the 0.9.4 changelog this issue describes just what we're
doing here:

http://docs.sqlalchemy.org/en/rel_1_0/changelog/changelog_09.html#change-c7ec7a2b899f6d33f4ef3c25d538daa0

and indeed the revision at
https://bitbucket.org/zzzeek/sqlalchemy/commits/825d3b0d6db4 is where
the test fails without the extra remote(). I don't really remember
what we're doing here and it would take me a few hours of staring to
re-understand this, it looks like the pattern you have is possibly the
reverse of what's being fixed here but nonetheless was impacted to do
"the right thing", which in your case was the thing you didn't want :).

>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
>

> *class C(Base):

> __tablename__ = 'c'
> id = Column(Integer, primary_key=True)

> parent_id = Column(ForeignKey('c.id <http://c.id>'))

> _del = Column("del", Integer, default=0)

> *

>
> def make_rel(cls, remoteCls, foreignKey, backref_name):
> br = backref(
> backref_name,
> collection_class=list,
> primaryjoin=and_(
> remoteCls.id == remote(getattr(cls, foreignKey)),

> cls._del == 0*# works with: remote(cls._del) == 0*

> )
> )
>
> rel = relationship(
> remoteCls,
> remote_side=remoteCls.id,
> primaryjoin=getattr(cls, foreignKey) == remoteCls.id,

> *post_update = True*,
> backref=br)
> return rel
>
> *C.parent = make_rel(C, C, "parent_id", "children")*

>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
>

> *c1, c2, c3 = C(), C(), C()
> c0 = C(children=[c1, c2, c3])*
>
> s.add(c0)
> s.commit()
>
> *c2._del = 1
> *s.commit()
>
>
> *assert c0.children == [c1, c3]*

> a_id = Column(ForeignKey('a.id <http://a.id>'))

> assert a1.bs <http://a1.bs> == [b1, b3]

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

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> <mailto:sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>

> <mailto:sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to a topic in
> the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/43rA8XsVuBQ/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.