Drizzle Team•12mo ago

Refactor transaction to batched expression

Im trying to refactor a transaction to a batched expression. I need help with basic sql concepts involving updating tables based on sub queries, and also drizzle syntax such as

sql`...`.as(...)`

sql`...`.as(...)`

. This is my current transaction to expire orders and release related stock:

await db.transaction(async (tx) => {
      // Get orders than are expired
      const expiredOrders = await tx
        .select({ id: orders.id })
        .from(orders)
        .where(
          and(
            // Only orders that are unpaid can be expired
            eq(orders.status, "UNPAID"),

            // Dates are stored as numbers and can be compared as such
            lt(orders.paymentDeadline, new Date()),
          ),
        )

      // Return early if there are no expired orders
      if (expiredOrders.length === 0) return "No expired orders" as const

      // Get a list of expired order IDs
      const expiredOrderIds = expiredOrders.map((eo) => eo.id)

      // Mark these orders as expired
      // const expireOrdersRes =
      await tx
        .update(orders)
        .set({
          status: "EXPIRED",
        })
        .where(inArray(orders.id, expiredOrderIds))

      // Get a list of products from expired orders, and the respective amount of stock
      const expiredProducts = await tx
        .select({
          id: orderProducts.productId,
          amount: sql<number>`count(*)`.as("amount"),
        })
        .from(orderProducts)
        .where(inArray(orderProducts.orderId, expiredOrderIds))
        .groupBy(orderProducts.productId)

      // Update these products' stock by the respective amounts
      // TODO: Batch these updates somehow
      expiredProducts.forEach(async (ep) => {
        await tx
          .update(products)
          .set({ stock: sql`${products.stock} + ${ep.amount}` })
          .where(eq(products.id, ep.id))
      })
    })

await db.transaction(async (tx) => {
      // Get orders than are expired
      const expiredOrders = await tx
        .select({ id: orders.id })
        .from(orders)
        .where(
          and(
            // Only orders that are unpaid can be expired
            eq(orders.status, "UNPAID"),

            // Dates are stored as numbers and can be compared as such
            lt(orders.paymentDeadline, new Date()),
          ),
        )

      // Return early if there are no expired orders
      if (expiredOrders.length === 0) return "No expired orders" as const

      // Get a list of expired order IDs
      const expiredOrderIds = expiredOrders.map((eo) => eo.id)

      // Mark these orders as expired
      // const expireOrdersRes =
      await tx
        .update(orders)
        .set({
          status: "EXPIRED",
        })
        .where(inArray(orders.id, expiredOrderIds))

      // Get a list of products from expired orders, and the respective amount of stock
      const expiredProducts = await tx
        .select({
          id: orderProducts.productId,
          amount: sql<number>`count(*)`.as("amount"),
        })
        .from(orderProducts)
        .where(inArray(orderProducts.orderId, expiredOrderIds))
        .groupBy(orderProducts.productId)

      // Update these products' stock by the respective amounts
      // TODO: Batch these updates somehow
      expiredProducts.forEach(async (ep) => {
        await tx
          .update(products)
          .set({ stock: sql`${products.stock} + ${ep.amount}` })
          .where(eq(products.id, ep.id))
      })
    })

6 Replies

LiquiFruitOP•12mo ago

This is what I've tried:

// [Sub query]
// Get all orders that are unpaid and have expired
const expiredOrdersSq = db
  .select({ id: orders.id })
  .from(orders)
  .where(
    and(
      // Only orders that are unpaid can be expired
      eq(orders.status, "UNPAID"),

      // Dates are stored as numbers and can be compared as such
      lt(orders.paymentDeadline, new Date()),
    ),
  )

// Use the list of expired orders to update them accordingly
// Can confirm this works
const expireOrders = db
  .update(orders)
  .set({
    status: "EXPIRED",
  })
  .where(inArray(orders.id, expiredOrdersSq))

// [Sub query]
// Get a list of {productId, count} rows of products that need stock released
// Can confirm this works as expected
const expiredOrdersProductsSq = db
  .select({ id: orderProducts.productId, amount: sql<number>`count(*)`.as("amount") })
  .from(orderProducts)
  .where(inArray(orderProducts.orderId, expiredOrdersSq))
  // this causes sql parsing errors, but I need it for the next step
  // .as("expired_orders_products_sq) 

 
// This used to work (before expiredOrdersProductsSq had two columns, before the amount column was added
const releaseProducts = db
  .update(products)
  .set({ stock: sql`${products.stock} + 1` }) // instead of `1` I want to use `${expiredOrdersProductsSq.amount}`
  .where(inArray(products.id, expiredOrdersProductsSq)) // this now throws "expected one column, got two"

// this batch is run as a transaction
return await db.batch([expireOrders, releaseProducts])

// [Sub query]
// Get all orders that are unpaid and have expired
const expiredOrdersSq = db
  .select({ id: orders.id })
  .from(orders)
  .where(
    and(
      // Only orders that are unpaid can be expired
      eq(orders.status, "UNPAID"),

      // Dates are stored as numbers and can be compared as such
      lt(orders.paymentDeadline, new Date()),
    ),
  )

// Use the list of expired orders to update them accordingly
// Can confirm this works
const expireOrders = db
  .update(orders)
  .set({
    status: "EXPIRED",
  })
  .where(inArray(orders.id, expiredOrdersSq))

// [Sub query]
// Get a list of {productId, count} rows of products that need stock released
// Can confirm this works as expected
const expiredOrdersProductsSq = db
  .select({ id: orderProducts.productId, amount: sql<number>`count(*)`.as("amount") })
  .from(orderProducts)
  .where(inArray(orderProducts.orderId, expiredOrdersSq))
  // this causes sql parsing errors, but I need it for the next step
  // .as("expired_orders_products_sq) 

 
// This used to work (before expiredOrdersProductsSq had two columns, before the amount column was added
const releaseProducts = db
  .update(products)
  .set({ stock: sql`${products.stock} + 1` }) // instead of `1` I want to use `${expiredOrdersProductsSq.amount}`
  .where(inArray(products.id, expiredOrdersProductsSq)) // this now throws "expected one column, got two"

// this batch is run as a transaction
return await db.batch([expireOrders, releaseProducts])

The worst part about the current solution (transaction), other than the round trips, is the looped updates. If someone could provide an improvement I might stick with that implementation.

Angelelz•12mo ago

I think the subquery solution is better, just 2 round trips to the db

const releaseProducts = db
  .update(products)
  .set({ stock: sql`${products.stock} + 1` }) // instead of `1` I want to use `${expiredOrdersProductsSq.amount}`
  .where(inArray(products.id, db.select({ id: expiredOrdersProductsSq.id }).from(expiredOrdersProductsSq)))

const releaseProducts = db
  .update(products)
  .set({ stock: sql`${products.stock} + 1` }) // instead of `1` I want to use `${expiredOrdersProductsSq.amount}`
  .where(inArray(products.id, db.select({ id: expiredOrdersProductsSq.id }).from(expiredOrdersProductsSq)))

This one should work This will be a lot better when we have:

db.with(/* several sq */).update(...).set(...)

db.with(/* several sq */).update(...).set(...)

That will make it great because the subqueries will be reused internally in the db.

Angelelz•12mo ago

There's an open PR for it https://github.com/drizzle-team/drizzle-orm/pull/1578

GitHub

Improve with clause by L-Mario564 · Pull Request #1578 · drizzle-...

Addresses #344 and #1541. This PR makes it so you can use a with clause alongside insert, update and delete. Things worth mentioning: MySQL doesn't support with ... insert. selectDistinct and ...

LiquiFruitOP•12mo ago

Thanks a lot Angelelz, will give this a go in the morning! @Angelelz please take a look at my comments. It's nearly working, but I think this is as far as I can get without the forementioned PR.

export async function _cleanUpExpiredOrders() {
  const expiredOrdersSq = db
    .select({ id: orders.id })
    .from(orders)
    .where(
      and(eq(orders.status, "UNPAID"), lt(orders.paymentDeadline, new Date())),
    )

  const expiredProductsSq = db
    .select({
      id: orderProducts.productId,
      amount: sql<number>`count(*)`.as("amount"),
    })
    .from(orderProducts)
    .where(inArray(orderProducts.orderId, expiredOrdersSq))
    .as("expired_products_sq")

  const releaseProducts = db
    .update(products)
    // this next line doesn't work as expected, the amount value is not interpreted
    .set({ stock: sql`${products.stock} + ${expiredProductsSq.amount}` })
    // this next line does work, but is not what I need
    // .set({ stock: 0 }) // correctly sets affected products' stock to 0
    .where(
      inArray(
        products.id,
        db.select({ id: expiredProductsSq.id }).from(expiredProductsSq),
      ),
    )

  return await db.batch([releaseProducts])
}

export async function _cleanUpExpiredOrders() {
  const expiredOrdersSq = db
    .select({ id: orders.id })
    .from(orders)
    .where(
      and(eq(orders.status, "UNPAID"), lt(orders.paymentDeadline, new Date())),
    )

  const expiredProductsSq = db
    .select({
      id: orderProducts.productId,
      amount: sql<number>`count(*)`.as("amount"),
    })
    .from(orderProducts)
    .where(inArray(orderProducts.orderId, expiredOrdersSq))
    .as("expired_products_sq")

  const releaseProducts = db
    .update(products)
    // this next line doesn't work as expected, the amount value is not interpreted
    .set({ stock: sql`${products.stock} + ${expiredProductsSq.amount}` })
    // this next line does work, but is not what I need
    // .set({ stock: 0 }) // correctly sets affected products' stock to 0
    .where(
      inArray(
        products.id,
        db.select({ id: expiredProductsSq.id }).from(expiredProductsSq),
      ),
    )

  return await db.batch([releaseProducts])
}

If I change the line in question to .set({ stock: sql’${expiredProductsSq.amount}’ }) And the drizzle studio, the stock field for that product is “amount” When it should be a number. I think the query is out of scope, especially because i am selecting only the id from it in the where clause. Is there a way i can run expiredProductsSq in the batch expression so that its “loaded” by the time i run releaseProducts?

Angelelz•12mo ago

I don't think queries in a batch have access to each other Yeah, this won't work, until we have update ... from That update query doesn't have access to expiredProductsSq It has to be included in the query somehow. The only way is with an update .. from query But you should be able to write it with db.execute(sql...)

LiquiFruitOP•12mo ago

Yea I think that's gonna be the best solution -- to write raw sql. Ill keep an eye on the PR and implement a drizzle solution as one becomes available. Thanks 💪

Gaming

Programming

Refactor transaction to batched expression