am trying to get user that have multiple Roles

[cyan orchid]

am trying to get user that have multiple Roles but what am getting is users with one role and the one with multiple roles get ignored i don't know why

    async getUsersWithRoles(page: number = 1, perPage: number = 10, roles: string[]): Promise<{ data: User[]; total: number }> {

        const skip = (page - 1) * perPage;

        const [users, total] = await this.UserRepository.findAndCount({
            where: { roles: In(roles) },
            skip,
            take: perPage,
        });

        return { data: users, total };
    }

[runic cypress]

is that typeorm? what database are you using?

[cyan orchid]

@runic cypress yes typeorm

[runic cypress]

what database do you use? PostgreSQL, MySQL, MariaDB, ...?

[cyan orchid]

@runic cypress it is MariaDB

this my roles schema

this is my request
Request

{
    "roles": [
        "Manager",
        "Admin"
    ],
    "page": 1,
    "perPage": 10
}

expected behavior is getting all users with admin and manager roles even the one that have multiple roles
what am getting is only users with one role either manager or admin

my users have those roles

this is the query log

query: SELECT `User`.`id` AS `User_id`, `User`.`username` AS `User_username`, `User`.`phone` AS `User_phone`, `User`.`password` AS `User_password`, `User`.`RefreshToken` AS `User_RefreshToken`, `User`.`roles` AS `User_roles`, `User`.`UserState` AS `User_UserState`, `User`.`createdAt` AS `User_createdAt`, `User`.`UpdatedAt` AS `User_UpdatedAt` FROM `users` `User` WHERE (`User`.`roles` IN (?, ?)) LIMIT 10 -- PARAMETERS: ["Manager","Admin"]
query: SELECT COUNT(1) AS `cnt` FROM `users` `User` WHERE (`User`.`roles` IN (?, ?)) -- PARAMETERS: ["Manager","Admin"]

[runic cypress]

I don't have any experience with the set data type in MariaDB. You probably need to execute multiple where clause with the FIND_IN_SET() function to make it happen.
https://mariadb.com/docs/server/ref/mdb/functions/FIND_IN_SET/