Right now a filter clause AssociationProxy == None
Consider the following code:
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, String
Base = declarative_base()
class A(Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
color = Column(String)
def __init__(self, color):
self.color = color
class B(Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('table_a.id'))
a_re = relationship('A', backref='b_re')
a_color = association_proxy('a_re', 'color')
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(engine)
session = Session()
Base.metadata.create_all(engine)
b1 = B()
b2 = B()
b3 = B()
b1.a_color = 'blue'
session.add_all([b1, b2, b3])
q = session.query(B).filter(B.a_color == None).all()
p = session.query(B).filter(B.a_color != None).all()
assert q == []
assert set(p) == set([b1, b2, b3])
I find it surprising that, when only b1 really has a color string through
B.a_color, filtering for B.a_color == None doesn't return b2 and b3, and
filtering for B.a_color != None returns b2 and b3 at all. The latter is
especially unintuitive.
Now I know what's going on:
>>> print B.a_color == None
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
>>> print B.a_color != None
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
The == clause requires that the relationship a_re has a valid target in the
first place before checking for a_re.color IS NULL, and the != is just the
negation of that. I understand that this is the desired action when the
right side is some non-null value like "blue", but in this case it should
be implemented along the lines of
>>> print B.a_color == None
table_b.a_id IS NULL
OR
EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL)
>>> print B.a_color != None
table_b.a_id IS NOT NULL
AND
NOT (EXISTS (SELECT 1
FROM table_a
WHERE table_a.id = table_b.a_id AND table_a.color IS NULL))
On a similar note, is there a way to get a_re.has() or equivalent through
only a_color? This can be important when a_re is supposed to be a private
variable, and only the string a_color is exposed. I originally thought that
!= None would do the trick but that doesn't work, as I've shown here.
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
