Help Query

[next wren]

model Product {
  productId       Int         @id @default(autoincrement())
  name            String
  description     String?
  price           Int
  status          ProductStatusType @default(ACTIVE)
  createdAt       DateTime    @default(now())

  storeId         Int
  store           Store       @relation(fields: [storeId], references: [storeId])

  payments        PaymentItem[]
}

enum ProductStatusType {
  ACTIVE
  DELETED
}

enum PaymentStatusType {
  PENDING
  SUCCESS
  FAILED
}

model Payment {
  paymentId       Int         @id @default(autoincrement())
  storeId         Int
  store           Store       @relation(fields: [storeId], references: [storeId])
  clientName      String
  clientEmail     String
  coupon          String      @default("")
  value           Int
  status          PaymentStatusType
  createdAt       DateTime    @default(now())

  paymentItems    PaymentItem[]
}

model PaymentItem {
  paymentId       Int
  productId       Int
  quantity        Int
  price           Int

  payment         Payment     @relation(fields: [paymentId], references: [paymentId])
  product         Product     @relation(fields: [productId], references: [productId])

  @@unique([paymentId, productId])
}

I have these 3 tables and I'm having difficulty getting the 5 best-selling products in a store.
Returning the product name, total product sales and total value.

I've already tried using findMany but it won't let me use _sum to add up the value of products in payments.

Can anyone help me tell which path I should follow?

[lilac bough]

sounds like maybe something a prisma extension can ddo

[elfin urchin]

You've done this and it doesn't work ?

const groupPaymentItems = await prisma.paymentItems.groupBy({
    by: ['productId'],
    _sum: {
        price: true,
    }
})

[next wren]

async function getTopSoldProducts(storeId) {
  try {
  const MostProducts = await prisma.paymentItem.groupBy({
    by: ["productId"],
    where: {
      product: {
        status: "ACTIVE",
      },
      payment: {
        storeId: storeId,
      },
    },
    _sum: {
      quantity: true,
      price: true,
    },
    take: 5,
    orderBy: {
      _sum: {
        quantity: 'desc'
      }
    }
  })
  return MostProducts;
} catch (error) {
    console.log(error);
}
}

Thanks, it worked, I hadn't tried it with groupBy.
Now in this function I wanted to have the name of the product, but I can't use select.
Is there any way or do I have to make another query?

[elfin urchin]

Can you try to add an :

include: {
  product: true
} 
// OR
include: {
  product: {
    select: {
      name: true
    }
  }
}

Because on your paymentItem you only have productId but you should be able to include the nested product information with your query