Drizzle Team•5mo ago

findMany with custom field

Currently, I have a code to fetch order details by aggregating several tables (Order, Customer, and Order Line Items tables). I've read the documentation about .findMany() and this is what I'm able to do

    const data = await db.query.orders.findMany({
      with: {
        customer: {
          columns: {
            fullName: true,
            phoneNumber: true,
          },
        },
        orderLineItems: {
          columns: {
            serviceId: true,
            qty: true,
          },
        },
      },
      columns: {
        id: true,
        status: true,
        totalPrice: true,
        createdAt: true,
      },
      where: eq(id, orders.id),
    });

    const data = await db.query.orders.findMany({
      with: {
        customer: {
          columns: {
            fullName: true,
            phoneNumber: true,
          },
        },
        orderLineItems: {
          columns: {
            serviceId: true,
            qty: true,
          },
        },
      },
      columns: {
        id: true,
        status: true,
        totalPrice: true,
        createdAt: true,
      },
      where: eq(id, orders.id),
    });

The code currently can return something like below

{
  id: 'ord_69N98NLF5Z5ZO16ST38PD',
  status: 'COMPLETED',
  totalPrice: 65000,
  createdAt: 2024-07-31T11:32:00.608Z,
  customer: { fullName: 'abbo', phoneNumber: '+123456780000' },
  orderLineItems: [
    { serviceId: 'svc_QC0OO1Z7ZPEGYP6P84EWI', qty: 1 },
    { serviceId: 'svc_ML00B6kXmt9f07oyYD71q', qty: 2 }
  ]
}

{
  id: 'ord_69N98NLF5Z5ZO16ST38PD',
  status: 'COMPLETED',
  totalPrice: 65000,
  createdAt: 2024-07-31T11:32:00.608Z,
  customer: { fullName: 'abbo', phoneNumber: '+123456780000' },
  orderLineItems: [
    { serviceId: 'svc_QC0OO1Z7ZPEGYP6P84EWI', qty: 1 },
    { serviceId: 'svc_ML00B6kXmt9f07oyYD71q', qty: 2 }
  ]
}

I want to fetch name from Service Table, and replace serviceId with it where serviceId = service.id. How can I achieve that? I've read https://orm.drizzle.team/docs/rqb#include-custom-fields but I still can't get it to work.

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Solution:

It's not possible with query api. But you can add a relation for Order Line Items table to get your service (you will then be able to add a with in orderLineItems). For the final mapping shape, it will be in pure JS....

Jump to solution

3 Replies

RorschOP•5mo ago

What i want is for it to look something like this

{
...
  orderLineItems: [
    { serviceName: 'Service A', qty: 1 },
    { serviceName: 'Service B', qty: 2 }
  ]
}

{
...
  orderLineItems: [
    { serviceName: 'Service A', qty: 1 },
    { serviceName: 'Service B', qty: 2 }
  ]
}

Solution

rphlmr ⚡•5mo ago

RorschOP•5mo ago

Thank you as always @Raphaël M (@rphlmr) ⚡ , I decided to use raw sql to handle this feature instead

Gaming

Programming

findMany with custom field