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();

10 Replies

Angelelz•13mo ago

In SQL you can't insert/update on 2 different table at the same time. You need to use 2 separate queries, one per table, preferably inside a transaction. See here: https://orm.drizzle.team/docs/transactions

Transactions – DrizzleORM

Drizzle ORM | %s

Angelelz•13mo ago

We don't know yet if drizzle will implement a convenience API in the future to do something like this.

zetashift•13mo ago

So I would have to do:

in a transaction:
- update table foo
- select and return foo joining on bar

in a transaction:
- update table foo
- select and return foo joining on bar

? (I don't need to insert/update 2 different tables at the same time, I would like to return data from 2 tables)

Angelelz•13mo ago

Oh, I understand. You want to update one table but then return a join? Yes, you would do it like this. Although not necessarily in a transaction. In this case, you can just run the update, make sure it went well, and then return the joined data

zetashift•13mo ago

yes exactly! If you have any example of that, that would be awesome 😛

Liltripple_reid•13mo ago

you can even use the RQB to return the join

Angelelz•13mo ago

function updateBar(email: string) {
  const updatedBar = await this.db
    .update(bars)
    .set({
      email: email,
    })
    .where(eq(bars.id, id))
    .returning();
  if (updatedBar.email === email) {
    const result = db.query.bars.findFirst({  // using relational queries API instead of join
      where: eq(bars.email, email),
      with: {
        foos: true
      }
    });
    return result;
  }
  return null; // or throw, however you want to handle an error
}

function updateBar(email: string) {
  const updatedBar = await this.db
    .update(bars)
    .set({
      email: email,
    })
    .where(eq(bars.id, id))
    .returning();
  if (updatedBar.email === email) {
    const result = db.query.bars.findFirst({  // using relational queries API instead of join
      where: eq(bars.email, email),
      with: {
        foos: true
      }
    });
    return result;
  }
  return null; // or throw, however you want to handle an error
}

zetashift•13mo ago

How stable is the API for this? From the docs I've read they looked like a 'nice-to-have' and am not sure if it's worth going through it since it's just 1 level of querying Thank you!!

Liltripple_reid•13mo ago

I mean it is stable as far as I know

zetashift•13mo ago

well guess I'll just go work in relation queries 😛 Is there some way to re-use the type of result? If there is a result I want to pass it to a function that transforms it to a DTO. But result doesn't typecheck as a Bar or a NewBar

Gaming

Programming

Select data from related table as well