In my database, I have three tables named product, branch, and product_branches.
The product table contains columns: id, name
The branch table contains columns: id, name, lat, lng
And the product_branches table contains columns: productId, branchId
I wrote a query to fetch the data as follows:
const query = this.createQueryBuilder('products')
.leftJoin('products.productBranches', 'productBranches')
.leftJoinAndSelect(
'branch',
'branches',
'productBranches.branchId = branches.id',
);
const products = await query.getMany();
The resulting JSON looks like this:
[
{
"id": "143f6e35-59ae-4185-bed2-a479ec716489",
"name": "product name",
},
.....]
However, I expect the result to look 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 print out the query using
console.log('query: ${query.getQuery()}');Below are the entity structures for reference:
@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 also tried using find method with relations but encountered the same issue.
Thank you