Eddy Vinck Comments - Answer Overflow

Eddy Vinck

Posts Comments

DTDrizzle Team

•Created by Eddy Vinck on 10/25/2024 in #help

aggregation core queries in extras?

I just realized.. it probably means I should do the whole query with the db.select syntax, right?

2 replies

DTDrizzle Team

•Created by Eddy Vinck on 6/13/2024 in #help

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

Thanks

5 replies

DTDrizzle Team

•Created by Eddy Vinck on 6/13/2024 in #help

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

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')`,
    ),
  );

5 replies

DTDrizzle Team

•Created by Eddy Vinck on 6/13/2024 in #help

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

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],
  }),
}));

5 replies

DTDrizzle Team

•Created by Eddy Vinck on 4/18/2024 in #help