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
jakeleventhal
jakeleventhalOP12mo 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
Angelelz12mo ago
This seems good to me. Was this maybe related with the issue with the sql from the other post?
jakeleventhal
jakeleventhalOP12mo 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
No description
jakeleventhal
jakeleventhalOP12mo ago
works fine for all other imports other than drizzle-orm will check shortly
Angelelz
Angelelz12mo ago
Autoimports depend too much on the environment you're on. Is drizzle-orm a dependecy in your closest package.json?
jakeleventhal
jakeleventhalOP12mo ago
do you also need drizzle-kit maybe? doubt it
Angelelz
Angelelz12mo ago
Definitely not
jakeleventhal
jakeleventhalOP12mo 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
Angelelz12mo 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
jakeleventhal
jakeleventhalOP12mo ago
bingo thanks
Angelelz
Angelelz12mo 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
jakeleventhal
jakeleventhalOP12mo ago
yeah this is awesome its amazing
Want results from more Discord servers?
Add your server