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