leftJoinAndSelect in nestJs does not retrieve all data from postgresql

Question

I have three tables product, branch and product_branches

product table contains: id, name

branch table contains: id, name, lat, lng

product_branches table contains: productId, branchId

I have this query

const query = this.createQueryBuilder('products')
  .leftJoin('products.productBranches', 'productBranches')
  .leftJoinAndSelect(
    'branch',
    'branches',
    'productBranches.branchId = branches.id',
  );


const products = await query.getMany();

the result appear like this

[
{
    "id": "143f6e35-59ae-4185-bed2-a479ec716489",
    "name": "product name",
},
.....]

but the result must be like this

[
{
    "id": "143f6e35-59ae-4185-bed2-a479ec716489",
    "name": "product name",
    "branches": [
       {
           "id": "143f6e35-59ae-4185-bed2-a479ec716489",
           "name": "branch name",
           "lat": "lat",
           "lng": "lng",
       },
       ....
    ]
},
....]

when I log the query console.log('query: ${query.getQuery()}'); and copy the result to postgresql, the query works fine and return the data

here is my entities:

@Entity()
export class Product {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @OneToMany((_type) => ProductBranches, (productBranches) => productBranches.product, {
    eager: false,
  })
  productBranches: ProductBranches[];
}

@Entity()
export class Branch {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  name: string;

  @Column()
  lat: string;

  @Column()
  lng: string;

  @OneToMany((_type) => ProductBranches, (productBranches) => productBranches.branch, { eager: false, },)
  productBranches: ProductBranches[];
}

@Entity()
export class ProductBranches {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToOne((_type) => Product, (product) => product.productBranches, {
    eager: true,
  })
  @Exclude({ toPlainOnly: true })
  product: string;

  @ManyToOne((_type) => Branch, (branch) => branch.productBranches, {
    eager: true,
  })
  @Exclude({ toPlainOnly: true })
  branch: string;
}

Note: I tried find with relations

Thanks

Answer 1

The issue is separated to two sides

1. @Exclude({ toPlainOnly: true }) on the branch: string; in ProductBranches Entity

2. using createQueryBuilder is not right in this situation, instead use

   const offers = this.find({
      relations: ['productBranches'],
   });
   

but the branch will be inside the productBranches not in a separate JSON object

Thanks to my friend Mohammad Ali