I have a post model that contains a list of items. The Prisma relation is shown below.

model Post {
  id        Int     @default(autoincrement()) @id
  title     String
  content   String?
  items     ItemsOnPosts[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Item {
  id     Int     @id @default(autoincrement())
  name   String
  posts  ItemsOnPosts[]
}

model ItemsOnPosts {
  post        Post     @relation(fields: [postId], references: [id])
  postId      Int      // relation scalar field (used in the `@relation` attribute above)
  item    Item @relation(fields: [itemId], references: [id])
  itemId  Int      // relation scalar field (used in the `@relation` attribute above)
  @@id([postId, itemId])
  notes   String?
  order   Int
}

The items within a post are ordered as per the

order

field in ItemsOnPosts. The problem arises when I want to add/ a new item in the the list, or want to change the order of items in a post. So, let’s say that a post (with id 1) has six items, with order as below.

postId  itemId  order
------  ------  -----
1       x       0 
1       x       1
1       x       2
1       x       3
1       x       4
1       x       5
2       x       0 
2       x       1
2       x       2
2       x       3

Two scenarios: - Add a new item to post 1 between 2 and 3. So this new item will become order number 3 and the order number of all items after will need to be incremented by 1. - Add a new item at the end. This would require me to get the order number of the last item in that post and then increment it by 1, i.e., 6. I know this is not a Prisma related problem. But I’m looking for the best way to maintain/update/handle these ordering of items in the post. Any insight/best practice is appreciated.

@Manish 👋 One way to do this would be first incrementing the order for each post by

1

that has the specific order that you want to insert. Then you could insert the value properly.

So: 1. Get all the items in a post along with their order number. 2. If the new item is being added at the end of list, just increment the highest order number by 1 and insert it. 3. If it is being inserted in the middle or if the order is being changed, then change the order numbers of that item and all items after that and then update all those records. Does this sound right?

You don’t need point 1. Just increment the order via

updateMany

and insert it. Only update the records from which point you will be inserting. So if you’re inserting at

2

you need to add a

where

to

updateMany

for

order

gte

2

Ok, I think I understand. So, once this

updateMany

is run, order position 2 will be empty and I can insert the new record with order 2. Is that correct?

Yes

inc

should work.

Ok, wonderful! Similarly, if an item is removed, then I can decrement. Thanks so much!

Yup!