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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.