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[];

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>`);

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?

jakeleventhalOP•1/7/24, 1:18 PM

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•1/7/24, 4:10 PM

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

jakeleventhalOP•1/7/24, 4:21 PM

@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

Screenshot_2024-01-07_at_11.20.35_AM.png

jakeleventhalOP•1/7/24, 4:24 PM

works fine for all other imports other than drizzle-orm

AAngelelz This seems good to me. Was this maybe related with the issue with the sql from t...

jakeleventhalOP•1/7/24, 4:26 PM

will check shortly

Jjakeleventhal @Angelelz any idea why this might not be auto importing properly? when i was exp...

Angelelz•1/7/24, 4:31 PM

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

jakeleventhalOP•1/7/24, 4:33 PM

do you also need drizzle-kit maybe?

jakeleventhalOP•1/7/24, 4:33 PM

doubt it

Angelelz•1/7/24, 4:33 PM

Definitely not

jakeleventhalOP•1/7/24, 6:32 PM

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

AAngelelz This seems good to me. Was this maybe related with the issue with the sql from t...

jakeleventhalOP•1/7/24, 7:02 PM

this is now resolved, but for some reason it says
PostgresError: aggregate functions are not allowed in GROUP BYPostgresError: aggregate functions are not allowed in GROUP BY

jakeleventhalOP•1/7/24, 7:02 PM

if i just do the entire query without drizzle, it works fine

Angelelz•1/7/24, 7:05 PM

This is a postgres error, if you're aggregating results you'll need to group them somehow

Angelelz•1/7/24, 7:06 PM

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•1/7/24, 7:07 PM

bingo

jakeleventhalOP•1/7/24, 7:07 PM

thanks

Angelelz•1/7/24, 7:13 PM

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•1/7/24, 7:44 PM

yeah this is awesome

jakeleventhalOP•1/7/24, 7:45 PM

its amazing

How to convert prisma $queryRaw to drizzle

Similar Threads

Similar Threads

Similar Threads