Drizzle Team•7mo ago

How to do an UPDATE with a JOIN in Drizzle ORM?

Something like this https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql I have this schema where I want to do an update on the "user_usage" table, but only if users.subscriptionId is a certain string or in a set of string values . I think what I want here is an UPDATE with a JOIN. Could not quite figure out how to do this with Drizzle ORM. Something like this from StackOverflow:

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;

UPDATE table_1 t1
SET foo = 'new_value'
FROM table_2 t2
    JOIN table_3 t3 ON t3.id = t2.t3_id
WHERE
    t2.id = t1.t2_id
    AND t3.bar = True;

But maybe a common table expression (CTE) could work like this example from StackOverflow?

with t as (
    select v.id as rowid, s.price_per_vehicle as calculatedvalue
    from vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id = s.id 
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid

with t as (
    select v.id as rowid, s.price_per_vehicle as calculatedvalue
    from vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id = s.id 
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid

Schema in next post

Stack Overflow

How to do an update + join in PostgreSQL?

Basically, I want to do this: update vehicles_vehicle v join shipments_shipment s on v.shipment_id=s.id set v.price=s.price_per_vehicle; I'm pretty sure that would work in MySQL (my backgro...

3 Replies

Eddy VinckOP•7mo ago

Schema:

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey().notNull(),
  email: varchar("email", { length: 256 }),
  stripeId: varchar("stripe_id", { length: 256 }),
  subscriptionId: varchar("subscription_id", { length: 256 }).default("unsubscribed"),
  paymentStatus: varchar("payment_status", { length: 256 }).default("none"),
  hasUsedTrial: boolean("has_used_trial").default(false),
});
export type User = InferSelectModel<typeof users>;

export const userUsages = pgTable("user_usage", {
  id: uuid("id").defaultRandom().primaryKey().notNull(),
  userId: uuid("user_id"),
  lastResetAt: timestamp("last_reset_at", { mode: "string" })
    .defaultNow()
    .notNull(),
  audioRecordingsMetered: integer("audio_recordings_metered"),
  audioRecordingsTotal: integer("audio_recordings_total"),
});

export const usersRelations = relations(users, ({ one }) => ({
  userUsage: one(userUsages, {
    fields: [users.id],
    references: [userUsages.userId],
  }),
  plan: one(plans, {
    fields: [users.subscriptionId],
    references: [plans.stripeLookupKey],
  }),
}));

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey().notNull(),
  email: varchar("email", { length: 256 }),
  stripeId: varchar("stripe_id", { length: 256 }),
  subscriptionId: varchar("subscription_id", { length: 256 }).default("unsubscribed"),
  paymentStatus: varchar("payment_status", { length: 256 }).default("none"),
  hasUsedTrial: boolean("has_used_trial").default(false),
});
export type User = InferSelectModel<typeof users>;

export const userUsages = pgTable("user_usage", {
  id: uuid("id").defaultRandom().primaryKey().notNull(),
  userId: uuid("user_id"),
  lastResetAt: timestamp("last_reset_at", { mode: "string" })
    .defaultNow()
    .notNull(),
  audioRecordingsMetered: integer("audio_recordings_metered"),
  audioRecordingsTotal: integer("audio_recordings_total"),
});

export const usersRelations = relations(users, ({ one }) => ({
  userUsage: one(userUsages, {
    fields: [users.id],
    references: [userUsages.userId],
  }),
  plan: one(plans, {
    fields: [users.subscriptionId],
    references: [plans.stripeLookupKey],
  }),
}));

Here is what I'm trying to do as a subquery

const payingUserIds = ctx.db
  .select({
    id: users.id,
  })
  .from(users)
  .where(inArray(users.subscriptionId, paidPlans))
  .getSQL();

const update = await ctx.db
  .update(userUsages)
  .set({
    lastResetAt: sql`CURRENT_TIMESTAMP`,
    audioRecordingsMetered: 0,
  })
  .where(
    and(
      inArray(userUsages.userId, payingUserIds),
      sql`${userUsages.lastResetAt} <= (CURRENT_DATE - INTERVAL '1 month')`,
    ),
  );

const payingUserIds = ctx.db
  .select({
    id: users.id,
  })
  .from(users)
  .where(inArray(users.subscriptionId, paidPlans))
  .getSQL();

const update = await ctx.db
  .update(userUsages)
  .set({
    lastResetAt: sql`CURRENT_TIMESTAMP`,
    audioRecordingsMetered: 0,
  })
  .where(
    and(
      inArray(userUsages.userId, payingUserIds),
      sql`${userUsages.lastResetAt} <= (CURRENT_DATE - INTERVAL '1 month')`,
    ),
  );

Sillvva•7mo ago

You don't need the .getSQL() on the subquery

Eddy VinckOP•7mo ago

Thanks

Gaming

Programming

How to do an UPDATE with a JOIN in Drizzle ORM?