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 wit开发者_StackOverflowhout Tags? What kind of query would I run to achieve that effect?

---

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.

---

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