DT
Drizzle Team•16mo ago
zetashift

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
Angelelz•16mo 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
Angelelz•16mo ago
We don't know yet if drizzle will implement a convenience API in the future to do something like this.
zetashift
zetashiftOP•16mo 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
Angelelz•16mo 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
zetashiftOP•16mo ago
yes exactly! If you have any example of that, that would be awesome 😛
Liltripple_reid
Liltripple_reid•16mo ago
you can even use the RQB to return the join
Angelelz
Angelelz•16mo 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
zetashiftOP•16mo 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
Liltripple_reid•16mo ago
I mean it is stable as far as I know
zetashift
zetashiftOP•16mo 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

Did you find this page helpful?