shahidcodes
shahidcodes
PostsComments
DTDrizzle Team
Created by shahidcodes on 4/13/2024 in #help
Fetching many to many along with some aggregation using select
I am trying to fetch many to many relation with drizzle select(). I need to use it because I also want to aggregate a value in nested relation ship Here is the schema 
  const results = await db
    .select({
    })
    .from(BatchModel)
    .fullJoin(BatchToCustomer, eq(BatchModel.id, BatchToCustomer.batch_id))
    .fullJoin(CustomerModel, eq(BatchToCustomer.customer_id, CustomerModel.id))
    .groupBy(BatchModel.id)
    .all();
  const results = await db
    .select({
    })
    .from(BatchModel)
    .fullJoin(BatchToCustomer, eq(BatchModel.id, BatchToCustomer.batch_id))
    .fullJoin(CustomerModel, eq(BatchToCustomer.customer_id, CustomerModel.id))
    .groupBy(BatchModel.id)
    .all();
Tried several things but could not wrap my head around how to return something like below 
[
  {
    name: "batch name",
    customers: [
      {
        name: "customer name",
        total: "item's total",
        items: [
          {
            name: "",
            price: "",
          },
        ],
      },
    ],
  },
];
[
  {
    name: "batch name",
    customers: [
      {
        name: "customer name",
        total: "item's total",
        items: [
          {
            name: "",
            price: "",
          },
        ],
      },
    ],
  },
];
16 replies