zetashift Posts - Answer Overflow

zetashift

Explore posts from servers

•Created by zetashift on 9/8/2023 in #help

Select data from related table as well

I have 2 tables:

export const foos = pgTable(
  "foos",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
    slug: varchar("slug", { length: 50 }).notNull(),
    fooData: jsonb("foo_data"),
  },
  (foos) => {
    return {
      slugKey: unique("slug_key").on(foos.slug),
    };
  },
);

export const bars = pgTable(
  "bars",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    activated: boolean("activated").default(false),
    email: text("email"),
    fooId: uuid("foo_id").references(() => foos.id),
  },
  (bars) => {
    return {
      activationCodeKey: unique("bars_activation_code_key").on(
        bars.activationCode,
      ),
      publicIdKey: unique("bars_public_id_key").on(bars.publicId),
    };
  },
);

export const foos = pgTable(
  "foos",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
    slug: varchar("slug", { length: 50 }).notNull(),
    fooData: jsonb("foo_data"),
  },
  (foos) => {
    return {
      slugKey: unique("slug_key").on(foos.slug),
    };
  },
);

export const bars = pgTable(
  "bars",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    activated: boolean("activated").default(false),
    email: text("email"),
    fooId: uuid("foo_id").references(() => foos.id),
  },
  (bars) => {
    return {
      activationCodeKey: unique("bars_activation_code_key").on(
        bars.activationCode,
      ),
      publicIdKey: unique("bars_public_id_key").on(bars.publicId),
    };
  },
);

Whenever I select a bar I would also like to return the associated foo.fooData . I believe I need a leftJoin for that, but how does that work when doing an update or create query? E.g. how would the following snippet also return the associated foo?

const result = await this.db
  .update(bars)
  .set({
    email: email,
  })
  .where(eq(bars.id, id))
  .returning();

const result = await this.db
  .update(bars)
  .set({
    email: email,
  })
  .where(eq(bars.id, id))
  .returning();

17 replies

Gaming

Programming