4 replies

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()

.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

name

from Service Table, and replace

serviceId

serviceId

with it

where serviceId = service.id

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

with

orderLineItems

orderLineItems

).
For the final mapping shape, it will be in pure JS.

Jump to solution

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()

.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

name

from Service Table, and replace

serviceId

serviceId

with it

where serviceId = service.id

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

with

orderLineItems

orderLineItems

).
For the final mapping shape, it will be in pure JS.

Jump to solution

findMany with custom field

findMany with custom field

Similar Threads

Similar Threads

Similar Threads