CODEWITHSUNDEEP
nestjsmany-to-manytypeorm
sivan m
sivan m

Reputation: 63

How to implement many-to-many query in typeorm

I'm working with typeORM with nestJS.

I have bi-directional many-to-many relationship between 2 tables: A and B (means a entity of A can be assign to many entities of B, and vice versa)

A.entity.ts:

import { Entity, Column, PrimaryGeneratedColumn,  ManyToMany, JoinTable } from 'typeorm';
import { B } from './B.entity';

@Entity('A')
export class A {
    @PrimaryGeneratedColumn()
    id: number;
    
    @Column()
    name: string;
   
    @ManyToMany(() => B, 
                (b)=>(b.AObjects))
    BObjects: B[];
}

B.entity.ts:

import { Entity, Column, PrimaryGeneratedColumn,  ManyToMany, JoinTable } from 'typeorm';
import { A} from './A.entity';

@Entity('B')
export class B{
    @PrimaryGeneratedColumn()
    id: number;
    
    @Column()
    name: string;

    @ManyToMany(() => A,
                 (a) => a.BObjects,
                 {eager:true})
    @JoinTable({
        name: 'AB',
        inverseJoinColumn : { name: 'Aid', referencedColumnName: 'id'},
        joinColumn: { name: 'Bid', referencedColumnName: 'id'},
    })
    AObjects: A[];
}

In the module service I want to implement a function, which receives a given id of B entity, and retreive all A's objects which refers to B's id

I want to write a typeORM query which implements the following sql query, for a given_B_id (which will be supplied as a parameter):

SELECT A.* 
from A, AB
where AB.Bid = given_B_id and A.id = AB.Aid

Will appreciate your help

Upvotes: 1

Views: 970

Answers (2)

sivan m
sivan m

Reputation: 63

I finally find a workaround solution.

But still will appreciate your feedback and advices about the best way to implement many-to-many request with constraint with TypeORM.

My workaround based on queryRunner of DataSource

in the service constructor: add private member :

@Injectable()
export class AService {
  constructor(
    @InjectRepository(A)
    private workerRepository: Repository<A>,
    private dataSource: DataSource
  ){}
// .....
}

and in the "@GET" route handler using

async getByB(given_B_id: number): Promise<A[]> {
     
  let  AObjects :Promise<A[]>
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();

  try {
    AObjects = await queryRunner.query(
        ` SELECT A.*
          FROM A, AB
          WHERE AB.Bid = ${given_B_id}
          AND A.id = AB.Aid`
      )
  } catch (ex) {
    throw ex;
  } finally {
    await queryRunner.release();
  }
    
  return(AObjects)

  };

Upvotes: 1

Soroush Ahrari
Soroush Ahrari

Reputation: 583

You can use find method as below

const res = this.BRepository.find({
    where: { id: given_B_id },
    relations: ['A']
});

Or use queryBuilder:

const res = this.BRepository
    .createQueryBuilder('b')
    .where('id = :b_id', { b_id: given_B_id })
    .leftJoinAndSelect('B.A', 'A')
    .getMany();

For more information check out the official TypeORM Docs.

Upvotes: 0

Related Questions