Drizzle Team•11mo ago

Update with join with Drizzle

I am using Postgres. Table Author has a 1 to 1 relations with table User. Author table has field userId that links to the primary key in the User table. Is there a way to write ONE update query to set the penName field in the Author table, but return all fields from both the Author and the User tables. Right now I need to write two queries

// Version 1
await db.update(author).set(request.body).where(eq(judge.id, request.params.id))

const updatedAuthor = await db.query.author.findFirst({
  where: eq(author.id, request.params.id),
  with: { user: true },
})
return updatedAuthor

// Version 2: Tried using .returning() but it also needs 2 queries
const [updated] = await db.update(author).set(request.body).where(eq(judge.id, request.params.id)).returning()

const toReturn = await db.query.author.findFirst({
  where: eq(author.id, updated.id),
  with: { user: true },
})
return toReturn

// Version 1
await db.update(author).set(request.body).where(eq(judge.id, request.params.id))

const updatedAuthor = await db.query.author.findFirst({
  where: eq(author.id, request.params.id),
  with: { user: true },
})
return updatedAuthor

// Version 2: Tried using .returning() but it also needs 2 queries
const [updated] = await db.update(author).set(request.body).where(eq(judge.id, request.params.id)).returning()

const toReturn = await db.query.author.findFirst({
  where: eq(author.id, updated.id),
  with: { user: true },
})
return toReturn

Or is two queries the standard way?

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Update with join with Drizzle