Drizzle Team•11mo ago

How to convert prisma $queryRaw to drizzle

const adjustmentResults = (await prisma.$queryRaw`
  SELECT
    DATE_TRUNC('month', ae.date, ${NA_TIMEZONE}) AS date,
    ae.marketplace,
    ae.type,
    SUM(ae.quantity * uc."cost") AS "adjustment"
  FROM "MasterProduct" AS mp
    JOIN "MasterProductUnitCost" AS uc ON (mp.id = uc."masterProductId")
    JOIN "InventoryItem" ii ON (uc."masterProductId" = ii."masterProductId")
    JOIN "AdjustmentEvent" AS ae ON (ii."groupId" = ae."groupId" AND ii.marketplace = ae.marketplace AND ii.sku = ae.sku)
  WHERE
    mp."userId" = ${userId}
    AND ii."userId" = ${userId}
    AND ae."userId" = ${userId}
    AND ae.date >= uc."fromDate"
    AND (uc."toDate" IS NULL OR ae.date < uc."toDate")
  GROUP BY 1, 2, 3
`) as MonthlyAdjustmentsData[];

const adjustmentResults = (await prisma.$queryRaw`
  SELECT
    DATE_TRUNC('month', ae.date, ${NA_TIMEZONE}) AS date,
    ae.marketplace,
    ae.type,
    SUM(ae.quantity * uc."cost") AS "adjustment"
  FROM "MasterProduct" AS mp
    JOIN "MasterProductUnitCost" AS uc ON (mp.id = uc."masterProductId")
    JOIN "InventoryItem" ii ON (uc."masterProductId" = ii."masterProductId")
    JOIN "AdjustmentEvent" AS ae ON (ii."groupId" = ae."groupId" AND ii.marketplace = ae.marketplace AND ii.sku = ae.sku)
  WHERE
    mp."userId" = ${userId}
    AND ii."userId" = ${userId}
    AND ae."userId" = ${userId}
    AND ae.date >= uc."fromDate"
    AND (uc."toDate" IS NULL OR ae.date < uc."toDate")
  GROUP BY 1, 2, 3
`) as MonthlyAdjustmentsData[];

I have a query like this. I want to just drop in the SQL the same way but can't seem to get it to work with

await db.execute(sql<MonthlyAdjustmentsData[]>`<sqlgoeshere>`);

await db.execute(sql<MonthlyAdjustmentsData[]>`<sqlgoeshere>`);

TypeError: query.getSQL is not a function

how am i supposed to do this?

12 Replies

jakeleventhalOP•11mo ago

i tried converting this to more drizzle-esque code:

await db
  .select({
    adjustment: sql`SUM(${adjustmentEvents.quantity} * ${masterProductUnitCosts.cost})`,
    date: sql`DATE_TRUNC('month', ${adjustmentEvents.date}, ${NA_TIMEZONE})`,
    marketplace: adjustmentEvents.marketplace,
    type: adjustmentEvents.type
  })
  .from(masterProducts)
  .innerJoin(masterProductUnitCosts, eq(masterProducts.id, masterProductUnitCosts.masterProductId))
  .innerJoin(inventoryItems, eq(masterProductUnitCosts.masterProductId, inventoryItems.masterProductId))
  .innerJoin(
    adjustmentEvents,
    and(
      eq(inventoryItems.groupId, adjustmentEvents.groupId),
      eq(inventoryItems.marketplace, adjustmentEvents.marketplace),
      eq(inventoryItems.sku, adjustmentEvents.sku)
    )
  )
  .where(
    and(
      eq(masterProducts.userId, userId),
      eq(inventoryItems.userId, userId),
      eq(adjustmentEvents.userId, userId),
      gte(adjustmentEvents.date, masterProductUnitCosts.fromDate),
      or(isNull(masterProductUnitCosts.toDate), lt(adjustmentEvents.date, masterProductUnitCosts.toDate))
    )
  )
  .groupBy(sql`1, 2, 3`)

await db
  .select({
    adjustment: sql`SUM(${adjustmentEvents.quantity} * ${masterProductUnitCosts.cost})`,
    date: sql`DATE_TRUNC('month', ${adjustmentEvents.date}, ${NA_TIMEZONE})`,
    marketplace: adjustmentEvents.marketplace,
    type: adjustmentEvents.type
  })
  .from(masterProducts)
  .innerJoin(masterProductUnitCosts, eq(masterProducts.id, masterProductUnitCosts.masterProductId))
  .innerJoin(inventoryItems, eq(masterProductUnitCosts.masterProductId, inventoryItems.masterProductId))
  .innerJoin(
    adjustmentEvents,
    and(
      eq(inventoryItems.groupId, adjustmentEvents.groupId),
      eq(inventoryItems.marketplace, adjustmentEvents.marketplace),
      eq(inventoryItems.sku, adjustmentEvents.sku)
    )
  )
  .where(
    and(
      eq(masterProducts.userId, userId),
      eq(inventoryItems.userId, userId),
      eq(adjustmentEvents.userId, userId),
      gte(adjustmentEvents.date, masterProductUnitCosts.fromDate),
      or(isNull(masterProductUnitCosts.toDate), lt(adjustmentEvents.date, masterProductUnitCosts.toDate))
    )
  )
  .groupBy(sql`1, 2, 3`)

RangeError: Maximum call stack size exceeded

Angelelz•11mo ago

This seems good to me. Was this maybe related with the issue with the sql from the other post?

jakeleventhalOP•11mo ago

@Angelelz any idea why this might not be auto importing properly? when i was exporting * it would import correctly. do i need to install some separate types package? seems wrong

jakeleventhalOP•11mo ago

works fine for all other imports other than drizzle-orm will check shortly

Angelelz•11mo ago

Autoimports depend too much on the environment you're on. Is drizzle-orm a dependecy in your closest package.json?

jakeleventhalOP•11mo ago

do you also need drizzle-kit maybe? doubt it

Angelelz•11mo ago

Definitely not

jakeleventhalOP•11mo ago

getting really strange behavior with this. reprod on other dev's computer. must be a tsconfig thing or something, but ive never seen such an issue. maybe some sort of bug in drizzle-orm? will put together a minimal repro and report back this is now resolved, but for some reason it says PostgresError: aggregate functions are not allowed in GROUP BY if i just do the entire query without drizzle, it works fine

Angelelz•11mo ago

This is a postgres error, if you're aggregating results you'll need to group them somehow In your original one, it seems like you're grouping by columns 1, 2 and 3, but I see that the order is different in the new one

jakeleventhalOP•11mo ago

bingo thanks

Angelelz•11mo ago

This is the type of query where drizzle shines. You can see how you have so much flexibility in creating your queries, without loosing types

jakeleventhalOP•11mo ago

yeah this is awesome its amazing

Gaming

Programming

How to convert prisma $queryRaw to drizzle