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]*
On Wed, Mar 16, 2016 at 12:42 PM, Mike Bayer <[email protected]
<mailto:[email protected]>> 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 <http://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 <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 [email protected]
<mailto:sqlalchemy%[email protected]>
<mailto:[email protected]
<mailto:sqlalchemy%[email protected]>>.
To post to this group, send email to [email protected]
<mailto:[email protected]>
<mailto:[email protected]
<mailto:[email protected]>>.
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
[email protected]
<mailto:sqlalchemy%[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
