"Utilizing Postgresql with TypeORM for filtering many-to-many relationships

Question

"Utilizing Postgresql with TypeORM for filtering many-to-many relationships

I have two entities that are connected with a ManyToMany relationship:

// Branch entity
 @ManyToMany(
        (type) => User,
        (e) => e.branches
    )
    users: User[];


// User entity
   @ManyToMany(
        (type) => Branch,
        (e) => e.users,
        {   eager: true,
            cascade: false }
    )
    
    @JoinTable()
    branches: Branch[];


    @IsEnum(Role)
    @Column('text', { default: Role.Client })
    role: Role;

I am looking to find branches where the users list does not contain a user with the role 'client'.

This functionality is needed for the following scenario:

[
 Branch {
    id: '98007770-c924-43cd-988c-774492e1e759',
    name: 'poslovnica1',
    users: [ {role:'client'},{role:'superAdmin'} ]
  },
 Branch {
    id: '787007770-c924-43cd-988c-774492e1e759',
    name: 'poslovnica13',
    users: [ {role:'client'},{role:'superAdmin'} ]
  },

  Branch {
    id: '36f5b1ad-6553-4b2f-936b-33fb4ca8e73e',
    name: 'poslovnica2',
    users: [ {role:'superAdmin' }]
  }
]

After filtering, the desired result is to retrieve all branches that do not have a user with the role 'client' or 'superAdmin':

[
   Branch {
    id: '36f5b1ad-6553-4b2f-936b-33fb4ca8e73e',
    name: 'poslovnica2',
    users: [ {role:'superAdmin'} ]
  }
]

typescript postgresql many-to-many nestjs typeorm

Answer 1

Answer №1

I was able to achieve a similar result using lodash, but approached it differently:

await branchRepository.find({
                    join: {
                        alias: 'branch',
                        leftJoinAndSelect: {
                            users: 'branch.users'
                        }
                    },
                    where: (qb) => {
                        qb.where('role != :role', { role: 'client' });
                    }
                });

However, my goal is to filter branches by users, not just users. Thanks for your effort!

Answer 2

I was able to achieve a similar result using lodash, but approached it differently:

await branchRepository.find({
                    join: {
                        alias: 'branch',
                        leftJoinAndSelect: {
                            users: 'branch.users'
                        }
                    },
                    where: (qb) => {
                        qb.where('role != :role', { role: 'client' });
                    }
                });

However, my goal is to filter branches by users, not just users. Thanks for your effort!

Answer 3

Answer №2

Welcome to the world of coding!

You were so close! Why not give this code a shot using lodash:

import {values, omit} from 'lodash';

// ....

const notClientRoles = values(omit(Role, Role.Client));

await this.branchRepository
    .createQueryBuilder('b')
    .leftJoin('b.users', 'users')
    .where('users.client IN(:...roles)', { roles: notClientRoles })
    .getMany();

Here are the details:

const notClientRoles = values(omit(Role, Role.Client))

This code snippet helps remove the Client role from the list using values and omit methods from lodash. We then use it in the where clause like so:

    .where('users.client IN(:...roles)', { roles: notClientRoles })

Let me know if this solution works for you :)

Answer 4

Welcome to the world of coding!

You were so close! Why not give this code a shot using lodash:

import {values, omit} from 'lodash';

// ....

const notClientRoles = values(omit(Role, Role.Client));

await this.branchRepository
    .createQueryBuilder('b')
    .leftJoin('b.users', 'users')
    .where('users.client IN(:...roles)', { roles: notClientRoles })
    .getMany();

Here are the details:

const notClientRoles = values(omit(Role, Role.Client))

This code snippet helps remove the Client role from the list using values and omit methods from lodash. We then use it in the where clause like so:

    .where('users.client IN(:...roles)', { roles: notClientRoles })

Let me know if this solution works for you :)

Answer 5

Answer №3

My desires are always fulfilled:

 result = await this.branchRepository.find({ relations: ['users'] });
 result = result.filter((branch: Branch) => {
                if (!branch.users.some((user) => user.role === Role.Client)) return branch;
                });

I am continually seeking ways to enhance this process...

Answer 6

My desires are always fulfilled:

 result = await this.branchRepository.find({ relations: ['users'] });
 result = result.filter((branch: Branch) => {
                if (!branch.users.some((user) => user.role === Role.Client)) return branch;
                });

I am continually seeking ways to enhance this process...

"Utilizing Postgresql with TypeORM for filtering many-to-many relationships

Answer №1

Answer №2

Answer №3

Similar questions

The TypeScriptLab.ts file is generating an error message at line 23, character 28, where it is expecting a comma

The click event triggered by the onclick clone/function may not always activate the click handler

What is the proper way to specify the type for a proxy that encapsulates a third-party class?

Issue with Typescript flow analysis when using a dictionary of functions with type dependency on the key

Tips for sending data to CSS in Angular

The SunEditor onChange event does not reflect updated state information

Using Generic Types in TypeScript for Conditional Logic

The 'doRequest' property is not found on the type 'any[]'

How to handle multiple formData input in NestJS controller

Boosting the performance of SELECT queries in a sizable PostgreSQL database with 250 million rows

MikroORM - Conditional join without foreign key constraints on the ID

Tips for locating precise information within nested object formations using Javascript

The specified type cannot be assigned to the type 'IntrinsicAttributes & MoralisProviderProps'

Issue encountered while accessing theme properties in a ReactJs component with Typescript

Is it possible for TypeScript to automatically determine the type of an imported module based on its path?

What is the best way to restrict the number of iterations in ngFor within Angular HTML

What could be preventing my state from changing to false when I click the close button on the menu?

How can we make type assertions consistent without sacrificing brevity?

Can we verify if strings can serve as valid property names for interfaces?

`Troubleshooting problem with debugging mocha tests in a TypeScript environment`