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
精彩评论