Drizzle Team•8mo ago

Nested update pattern

What would be the best pattern to replace this query I had using Prisma. It updates a collection nested in a space. Here's a screenshot of the query and what I've used to replace it.

2 Replies

Mykhailo•8mo ago

Hello, @wkd9241! I assume, that collections have spaceId or some column that references to space. So you can update collections straight without need to query space. Could you clarify the logic with name and oldName? I think you can do smth like this:

  await db
    .update(collections)
    .set({ name: collection.name })
    .where(and(eq(collections.spaceId, spaceId), eq(collections.name, collection.oldName)));

  await db
    .update(collections)
    .set({ name: collection.name })
    .where(and(eq(collections.spaceId, spaceId), eq(collections.name, collection.oldName)));

wkd9241OP•8mo ago

No, the thing is that the collections are a column in the space table. They aren't a table. They look like this:

type Collection = {
  name: string
  parent?: string | null
  products: string[]
}

// with this as my Space schema
const space = pgTable('spaces', {
  {...}
  collections: json('collections').$type<Collection[]>().notNull(),
})

type Collection = {
  name: string
  parent?: string | null
  products: string[]
}

// with this as my Space schema
const space = pgTable('spaces', {
  {...}
  collections: json('collections').$type<Collection[]>().notNull(),
})

As for the name and oldName logic, using trpc, I send the new data of the collection with the oldName property that is the name of the collection before the updating (it might not change). Then, I use this oldName as a pseudo-identifier to know which collection to update. Each collection has a unique name. Now that I double-check the code, I should destructure the updated collection data to extract the oldName property before assigning it as the new collection's data (during the mapping in the Drizzle transaction) With Prisma I could update the collections within a Space as shown in the screenshot. With Drizzle, I havn't found a more concise way of doing it then by what you see there (minding the correction I just mentionned).

Gaming

Programming

Nested update pattern