How to add an automatic filter to a relation with SQLAlchemy?

Question

I'm using SQLAlchemy 0.5rc, and I'd like to add an automatic filter to a relation, so that every time it tries to fetch records for that relation, it ignores the "remote" ones if they're flagged as "logically_deleted" (a boolean field of the child table)

For example, if an object "parent" has a "children" relation that has 3 records, but one of them is logically deleted, when I query for "Parent" I'd like SQLA to fetch the parent object with just two children..
How should I do it? By adding an "and" condition to the primaryjoin parameter of the relation? (e.g. "Children.parent_id == Parent.id and Children.logically_deleted == False", but is it correct to write "and" in this way?)

Edit:
I managed to do it in this way

children = relation("Children", primaryjoin=and_(id == Children.parent_id, Children.logically_deleted==False))

but is there a way to use a string as primaryjoin instead?

Answer 1

but is there a way to use a string as primaryjoin instead?

You can use the following:

children = relationship("Children", primaryjoin="and_(Parent.id==Children.parent_id, Children.logically_deleted==False)"

This worked for me!

Answer 2

The and_() function is the correct way to do logical conjunctions in SQLAlchemy, together with the & operator, but be careful with the latter as it has surprising precedence rules, i.e. higher precedence than comparison operators.

You could also use a string as a primary join with the text() constructor, but that will make your code break with any table aliasing that comes with eagerloading and joins.

For logical deletion, it might be better to map the whole class over a select that ignores deleted values:

mapper(Something, select([sometable], sometable.c.deleted == False))