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
<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]*
On Wed, Mar 16, 2016 at 12:42 PM, Mike Bayer <clas...@zzzcomputing.com>
wrote:
>
>
> 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 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+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.
>
--
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.
