LiquiFruit Comments - Answer Overflow

Thanks for the help but Im not sure this is what Im looking for. I typed my data access layer function getAssociationByIdFromDb as the Promise<type of associations.$inferSelect | undefined> but it doesn't solve my issue as seen below.

11 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

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 💪

19 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

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?

19 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

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.

19 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

@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])
}

19 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

Thanks a lot Angelelz, will give this a go in the morning!

19 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

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.

19 replies

DTDrizzle Team

•Created by LiquiFruit on 12/5/2023 in #help

Refactor transaction to batched expression

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])

19 replies

DTDrizzle Team

•Created by adrtivv on 11/27/2023 in #help

drizzle studio blank screen

Just found out that this happened in Chrome but not Edge. Could be worth testing.

11 replies

DTDrizzle Team

•Created by adrtivv on 11/27/2023 in #help

drizzle studio blank screen

Can confirm this is happening to me using Drizzle + Turso/SQLite.

11 replies

Gaming

Programming