How to select filtering by associated entries collection in sqlalchemy

I have many-to-many relationships for users and roles and I want to select user which have specific roles using realtions. For example I want to get user having:

roles = ["role_1", "role_2", "role_3"]

so I tried

query.filter(Users.roles.contains(roles))

(where roles – List[Roles])

but I got

sqlalchemy.exc.ArgumentError: Mapped instance expected for relationship comparison to object.   Classes, queries and other SQL elements are not accepted in this context; for comparison with a subquery, use Users.roles.has(**criteria).

then I tried

    query.filter(Users.roles.has(Roles.name.in_(roles)))

where roles already List[str]

And I got

sqlalchemy.exc.InvalidRequestError: 'has()' not implemented for collections.  Use any().

but any() selects entry that has any associated role when I need entry that has all required roles. So how to select it right way using relationships instead of joins and etc.?

  class Users(sa.Model):
__tablename__ = 'users'

id = Column(Integer, primary_key=True, autoincrement=True)
login = Column(String(50), unique=False)

roles = relationship('Roles', secondary='user_roles_map',
                     cascade='all, delete')


class Roles(sa.Model):
__tablename__ = 'roles'

id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(40), unique=True)



class UserRolesMap(sa.Model):
__tablename__ = 'user_roles_map'

id_seq = Sequence(__tablename__ + "_id_seq")
id = Column(Integer(), id_seq, server_default=id_seq.next_value(),
            unique=True, nullable=False)
user_id = Column(
    Integer, ForeignKey('users.id'),
    primary_key=True)
role_id = Column(
    Integer, ForeignKey('roles.id'),
    primary_key=True)

Answer

I didn’t find what I was looking for, so for now I just wrote it with joins:

    query = db_session.query(Users) 
        .filter_by(**parameters)
        .join(UserRolesMap, UserRolesMap.user_id == Users.id)
        .filter(UserRolesMap.role_id.in_(roles_ids))
        .group_by(Users)
        .having(func.count(UserRolesMap.role_id) >= len(roles_ids))

where roles_ids was collected from Roles table before. And if you need user with only required roles you can replace “>=” with “==”.