SQLAlchemy: filter by relationship

Question

I have 2 tables, User and Object, which have a one-to-many relationship (a User can have many objects ).

How can I filter for users that have at least one object, in a pep8 compliant way?

This code works, but is not pep8-compliant:

query = session.query(User.id)
query = query.filter(User.objects != None)

The documentation mentions using isnot: http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#common-filter-operators

But the following code gives rise to a not implemented error.

query = session.query(User.id)
query = query.filter(User.objects.isnot(None))

Answer 1

As you pointed out, isnot is not implemented for relationships, but only for simple columns.

As for relationships, there is a general even more powerful construct any(criterion, ...).

In your case you can write the PEP8-compliant code below, which will produce exactly the same SQL as in your question:

q = session.query(User.id)
q = q.filter(User.objects.any())

But it also allows you to do more complicated queries, like: return Users, which do not have objects with value > 100:

q = session.query(User.id)
q = q.filter(~User.objects.any(Object.value > 100))