CODEWITHSUNDEEP
pythonsqlsqlalchemymany-to-many
a.rahmanovsky
a.rahmanovsky

Reputation: 57

How to intersect values in many-to-many relationship sqlalchemy?

I have table Teacher. It contains many-to-many relationship with table Student. Student contains unique column name. how can I find all the teachers that contain students with certain names?
For example:
Teacher1 contains Student1 with name "Bob" and Student2 with name "Alice".
Teacher2 contains Student2 with name "Alice" and Student3 with name "Mark".
Teacher3 contains Student1 with name "Bob".
Teacher4 contains Student3 with name "Mark".
I get names ["Alice", "Mark"].
In this example I have to get Teacher 1, 2, 4.
How to write this sqlalchemy query?
session.query(Teacher).filter(...).all()?

Upvotes: 1

Views: 291

Answers (1)

jorzel
jorzel

Reputation: 1346

Asumming that your association many-to-many model/tabele is StudentTeacher and you looking for names=("Alice", "Mark",), this query should return what you expected:

results = session.query(
   Teacher
).join(StudentTeacher).join(Student).filter(
   Student.name.in_(names)
).all()

Upvotes: 1

Related Questions