How do I query an array in TypeORM
Solution 1
You can use PostgreSQL array functions
let user = await this.userRepository
.createQueryBuilder('user')
.where('user.permissions @> ARRAY[:permissions]', { permissions: UserPermission.SUPERADMIN})
.getOne();
Also, if you need to look for the presence of multiple array elements, you can use a spread operator (notice the ...
):
.where('user.permissions @> ARRAY[:...permissions]', { permissions: ['foo', 'bar']})
Solution 2
As Mykhalio mentioned , you can use the PostgreSQL array functions to do the trick.
Adding few more pointers.
- If you want to check users with both USER_ADMIN and SUPER_ADMIN permissions.
.where('user.permissions @> :permissions', { permissions:['USER_ADMIN', 'SUPER_ADMIN']})
- If you want to check users with either USER_ADMIN or SUPER_ADMIN permissions.
.where('user.permissions && :permissions', { permissions: ['USER_ADMIN', 'SUPER_ADMIN']})
Solution 3
Why not use this approach. Works for me with zero errors.
let roles = ['ADMIN', 'STUDENT', SECRETARY];
let user = await this.userRepository.createQueryBuilder('user')
.andWhere('user.roles IN (:...roles)')
.setParameter('roles', ...roles).count()
Admin
Updated on December 12, 2021Comments
-
Admin over 2 years
I want to create user permissions management. I use TypeORM with PostgreSQL. This is the column definition for the permissions within the user entity:
@Column({ type: 'text', array: true }) permissions: UserPermission[] = [];
This is the
UserPermission
enum:export enum UserPermission { APP_USER = 'APP_USER', USER_ADMIN = 'USER_ADMIN', SUPERADMIN = 'SUPERADMIN' }
I want to find one user who has the
'SUPERADMIN'
permission but I cannot find the right spot in the documentation / github issues which explains how to do this. I already spent over an hour on this and I suppose this is a simple task.Is there something like "Includes" to check if the permissions array includes a specific element and/or includes multiple elements?
const user = await this.userRepository.findOne({ where: { permissions: Includes('SUPERADMIN') } });
I would be very thankful if someone could point me to the correct documentation page :)
Edit:
The following works for me but I think it is not optimal yet:
@Column('simple-json') permissions: string[];
let user = await this.userRepository.createQueryBuilder('user') .where('user.permissions like :permissions', { permissions: `%"${UserPermission.SUPERADMIN}"%` }) .getOne();