CODEWITHSUNDEEP
pythonsqlalchemy
bard
bard

Reputation: 3072

SQLAlchemy: filter by relationship

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))

Upvotes: 12

Views: 12572

Answers (2)

van
van

Reputation: 77092

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))

Upvotes: 17

Alexander Litvinenko
Alexander Litvinenko

Reputation: 319

As you may know, in Django such filtering can be done in simple, declarative way:

Entry.objects.filter(blog__name='Beatles Blog')

And I always dreamt to have something like this in SQLAlchemy.

Good news for you: few weeks ago, I created package for that: https://github.com/absent1706/sqlalchemy-mixins#django-like-queries

Also package contains very useful batteries: Active Record for SQLAlchemy as well as nested eager loading and readable __repr__.

Upvotes: -5

Related Questions