Support ordering of model/relations

[Sytten]

Problem

Original discussion: https://prisma.slack.com/archives/CKQTGR6T0/p1585298279075900

Imagine a schema like:

model Checklist {
    id            String          @default(cuid()) @id
    items         ChecklistItem[]
}
model ChecklistItem {
    id          String              @default(cuid()) @id
    content     String
    checklist   Checklist
}

Currently, there is not guaranty as to the order of ChecklistItem. The current work around is to create an ordering column and order by this column. It would be awesome if prisma could have a way to somewhat automatically do that.

Propositions

1. Order the virtual relation:
   A new directive could be added: @ordered(by: String). This would be applied to the virtual relation items ChecklistItem[] and would reference an explicit column in the model ChecklistItem. This still leaves use with the problem of filling the column though and maintaining the order.
2. Order the model:
   A new directive could be added: @order. This would be applied to an Int column on the ChecklistItem. When creating a new item, prisma would make sure to insert the item at the right place and manage the int placed in it. See proposition 3 to see how.
3. Order via the client:
   New arguments could be added to create and update (delete can be managed internally) inside ChecklistItemCreateArgs and ChecklistItemUpdateArgs. Those would be placement constraint for the model: after and/or before. Each one would be a new type that would be something like:

export type ChecklistItemOrderUniqueInput = {
  id?: number | null
}

A user could then specify between each currently created items he wants to place this new item or move an existing item. For example:

prisma.checklistItem.create({ data: { content: "My new item" }, after: { id: 5 } })

[tvvignesh]

Great elaboration @Sytten

I would just like to point out that internally, it may be handled as linked lists rather than storing indexes since the index will also change every time one of the elements is deleted from the list.

It is something like the problem which I have mentioned in this issue: prisma/prisma-client-js#589

[janpio]

Does anyone have examples of other ORMs or database clients implementing something like this?

[Sytten]

No idea about ordering in other ORMs but I had to implement that in my rust codebase a little while ago.
I view that feature similar to cursor based support that prisma is kinda unique in providing.

You have globally two of approaches to do this that avoid write amplification (aka you don't need to update all siblings):

• Lexical ordering. Atlassian Lexorank is a good place to get started on that topic. You can avoid rebalancing if you accept infinite strings.
• Fractions ordering. Pinterest is kinda doing that. You can store them in very large int or float.

I decided to go with lexical because it is the simplest (I built a custom rust one but mudders exists), but you might prefer the numerical way.