Keep order of a list of items in prisma
I have an app that allows the user to reorder a list of items, with each item being a prisma
model. To facilitate this, I have an index field that I can then order the items by when fetching. The problem with this approach is I have to update each item every time I delete or add an item. I'm wandering if:
- there's a built-in way to do this in prisma?
- is there a better way of doing this myself?
My model looks something like:
model Album {
id String @id
index Int
title String?
}
6 Replies
Unknown User•2y ago
Message Not Public
Sign In & Join Server To View
Could you just make a model for your list and store the order in json as an array of indexes?
kinda hacky but one way to do it is to increment the index by 128 instead of 1. This effectively lets you slot things in anywhere by changing its index between 2 other indicies. at some point if the user keeps reordering things you will eventually need to reassign the indexes to everything, but for lots of applications this might never happen and might take a long time
i believe this is technically the best way o do it algorithmically, but from a simplicity standpoint doing what @zthomas recommended or just continuing to redo the indexes is probably best in most cases.
im just giving another option (albeit a much more complicated one)
So something like
And just set the order array every time an item is added, moved, or deleted?
it depends on the requirements of the application but I would imagine a model for Album and AlbumCollection with a many-to-many relationship. the AlbumCollection would have a JSON field with an array of indexes. this way an album could be part of many collections and each collection could maintain a unique order. If an album for whatever reason is removed from the db it doesn't matter so much that it's index is orphaned in the JSON array.
but yes, you would re-order the array whenever an item is added/moved/deleted. and it would use far fewer writes
Thanks for the responses. I did a bit more research and found another method which stores a foreign key reference for the next record (https://stackoverflow.com/questions/4115053/how-to-keep-ordering-of-records-in-a-database-table). This seems like a good solution since you only have to update one record with each add and delete and 2 with a reorder. I'll try this i think, though I also like @zthomas's solution.
Stack Overflow
How to keep ordering of records in a database table
i am creating a database table that is going to store menu links that will ultimately show up on a web page.
My issue is that i want to control the order of the menu items. I could have a field c...