CODEWITHSUNDEEP
pythonsqldatabase-designsqlalchemypylons
john
john

Reputation: 3103

Python, SqlAlchemy: Many to many relationship, find those without

Say I have two types of objects, Movies and Tags, related by the ORM in a many to many relationship with an association table indicated by the secondary argument to relationship(), and I want to be able to find, say, all the Movies without Tags? What kind of query would I run to achieve that effect?

Upvotes: 2

Views: 257

Answers (2)

van
van

Reputation: 76992

Use any(), which is specifically useful for this kind of queries.

query = session.query(Movies).filter(~Movies.tags.any())

Here tags is the many-to-many relationship name to Tags.

Upvotes: 2

amelvin
amelvin

Reputation: 9051

If the TagId was part of the Movies object, this would be a simple case of checking if the TagId on Movies was null:

session.query(Movies).filter(Movies.TagId == None).all()

But your many-to-many comment suggests a MoviesTags link entity, so its trickier and involves linking to MoviesTags using a left outer join.

There's a good guide to writing and debugging similar SQL queries. And you basically want to end up with:

select Movies.name from Movies 
left outer join MoviesTags on Movies.MovieId = MoviesTags.MovieId 
where MoviesTags.TagId is null

Upvotes: 2

Related Questions