Drizzle Team•10mo ago

feedback wanted: subquery in update

This is what I'm trying to do. I still need to make a few other adjustments to my app before I can run it, but are there any things I should be doing differently here? I want to use a subquery to avoid the network roundtrip of just a bunch of user IDs I'm using MySQL if that makes a difference.

const allSubscribedUsersIds = db
  .select({
    id: users.id,
  })
  .from(users)
  .where(
    and(
      isNotNull(users.subscriptionId),
      ne(users.subscriptionId, SubscriptionPlan.unsubscribed),
    ),
  )
  .getSQL();

const update = await db
  .update(userUsages)
  .set({
    lastResetAt: sql`CURRENT_TIMESTAMP`,
    audioRecordingsMetered: 0,
  })
  .where(
    and(
      inArray(userUsages.userId, allSubscribedUsersIds),
      sql`${userUsages.lastResetAt} <= CURDATE() - INTERVAL 1 MONTH`,
    ),
  );

const allSubscribedUsersIds = db
  .select({
    id: users.id,
  })
  .from(users)
  .where(
    and(
      isNotNull(users.subscriptionId),
      ne(users.subscriptionId, SubscriptionPlan.unsubscribed),
    ),
  )
  .getSQL();

const update = await db
  .update(userUsages)
  .set({
    lastResetAt: sql`CURRENT_TIMESTAMP`,
    audioRecordingsMetered: 0,
  })
  .where(
    and(
      inArray(userUsages.userId, allSubscribedUsersIds),
      sql`${userUsages.lastResetAt} <= CURDATE() - INTERVAL 1 MONTH`,
    ),
  );

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

feedback wanted: subquery in update