Query many-to-many relationships with extra columns using typeOrm

Question

Using typeorm and trying to figure out how to query many-to-many relationships, i have two tables :
product(id,name...etc) <---many-to-many---> stash(id,name...etc)
the join table has two additional columns: expirationDate and quantity
I have already setp the relationships :

@Entity('stash')
export class Stash {
    @PrimaryGeneratedColumn()
    id?: number;

    @Column("text")
    name!: string;

    @OneToMany(type => productStash, ps => ps.stash)
    public productStashes?: Relation<ProductStash[]>;
    
}

@Entity('product')
export class Product {
    @PrimaryGeneratedColumn()
    id?: number;

    @Column("text")
    name!: string;

    @OneToMany(type => productStash, ps => ps.prudct)
    public productStashes?: Relation<ProductStash[]>;
    
}

Join table :

@Entity('product_stash')
export class ProductStash {
    @Column("integer")
    @PrimaryColumn({ name: 'ingredientId' })
    ingredientId?: number;

    @Column("integer")
    @PrimaryColumn({ name: 'stashId' })
    stashId?: number;

    @Column("integer", { default: 1 })
    quantity?: number;

    @Column("text")
    expirationDate!: Date;

    @ManyToOne(type => Product, p => p.productStashes)
    product?: Relation<Product>

    @ManyToOne(type => Stash, s => s.productStashes)
    stash?: Relation<Stash>;
}

Now when i try to query a stash and all it's products i get :

    repository.createQueryBuilder("s")
      .innerJoinAndSelect("s.productStashes", "sp")
      .innerJoinAndSelect("sp.product", "p")
      .where("s.id = :id", { id: stashId })
      .getOne();

I get :

{
   "id":1,
   "name":"stash_1",
   "productStashes":[
      {
         "quantity":1,
         "expirationDate":"2023-07-11",
         "product":{
            "id":1,
            "name":"Computer"
         }
      },
      {
         "quantity":1,
         "expirationDate":"2023-07-11 19:06:58.861",
         "product":{
            "id":2,
            "name":"Mouse"
         }
      }
   ]
}

But what i want is for the extra columns in the join table (expirationDate, quantity) to be in the product object, or maybe just flatten the product object to get something like this :

{
   "id":1,
   "name":"stash_1",
   "productStashes":[
      {
         "quantity":1,
         "expirationDate":"2023-07-11",
         "id":1,
         "name":"Computer"
      },
      {
         "quantity":1,
         "expirationDate":"2023-07-11 19:06:58.861",
         "id":2,
         "name":"Mouse"
      }
   ]
}

Any idea on how i can do it ?

Answer 1

To achieve the desired result where the extra columns from the join table (expirationDate and quantity) are included in the Product objects or the Product objects are flattened within the Stash object, you can modify your query and entity definitions accordingly. Here's how you can do it:

1. Modify your Stash entity to include a property for productStashes as an array of objects that contain both the Product properties and the join table properties.

   @Entity('stash') export class Stash { @PrimaryGeneratedColumn() id?: number;

    @Column("text")
    name!: string;
   
    @OneToMany(type => ProductStash, ps => ps.stash, { eager: true })
    productStashes?: ProductStash[]; // Use eager loading to load related entities
   

   }

2. Modify your query to fetch the required columns from the join table and product, and then format the result as desired:

   const stash = await repository.createQueryBuilder("s") .leftJoin("s.productStashes", "ps") // Use left join to include all stashes, even those with no product stashes .leftJoin("ps.product", "p") .where("s.id = :id", { id: stashId }) .getOne();

   // Format the result as desired const formattedStash = { id: stash.id, name: stash.name, productStashes: stash.productStashes.map(ps => ({ quantity: ps.quantity, expirationDate: ps.expirationDate, ...ps.product, // Spread the Product properties })), };

   console.log(formattedStash);

This code will retrieve the stash and its associated product stashes, including the extra columns (expirationDate and quantity) from the join table, and then format the result as you specified.