SQLAlchemy: filter by membership in at least one many-to-many related table

OK, after a lot of research, I realized that it was my own ignorance of SQL terminology that was holding me back. My search for a solution to find users belonging to "at least one of" the list of groups should have been to find users belonging to "any" of the list of groups. The any ORM function from SQLAlchemy does exactly what I needed, like so:

session.query(ZKUser).filter(ZKUser.groups.any(ZKGroup.id.in_([1,2,3])))

That code emits this SQL (on MySQL 5.1):

SELECT * FROM users WHERE EXISTS (    SELECT 1 FROM user_groups, groups     WHERE users.id = user_groups.contact_id         AND groups.id = user_groups.group_id         AND groups.id IN (%s, %s, %s)    )

According to the docs for any, the query will run faster if you use an explicit join instead:

Because any() uses a correlated subquery, its performance is not nearly as good when compared against large target tables as that of using a join.

In your case, you could do something like:

users = (    session.query(ZKUser)    .join(user_groups)    .filter(user_groups.columns.group_id.in_([1, 2, 3])))

This emits SQL like:

SELECT *FROM usersJOIN user_groups ON users.id = user_groups.user_id WHERE user_groups.group_id IN (1, 2, 3)

You can use in_:

session.query(ZKUser).filter(ZKGroup.id.in_([1,2])).all()