Support natural ordering for many to many relationship

[conduongtong]

Problem

You can read the problem in here: #22561
This problem causes incorrect ordering in one-to-many and many-to-many relationships.

Suggested solution

Maybe when using:

await prisma.post.findFirst({ include: { tags: true } });

It will return id of many to many relation table instead of sorting id of A or B

[janpio]

Without an explicit orderBy you will not get any guarantees by Prisma in which order the data is returned.

The primary key here also has no explicit meaning, as when you create the data as shown in the discussion with await prisma.post.update({ where: { id: 1 }, data: { tags: { connect: [{ id: 2 }, { id: 1 }, { id: 3 }] } } }); there is no guarantee in which the data will be created, and hence the primary keys assigned.

Do you have example of other tools that show how they achieve what you suggest?

[janpio]

After asking my colleagues internally, I am closing this issue now as we all agree that Prisma can not guarantee any order unless you ask it to order the data a certain way. Additionally, for implicit m:n relations there is no way to add such an order field - so you would need to fall back to using en explicit m:n relation where you have that control and then can also use orderBy.

(Happy to continue the discussion in the closed issue of course, I don't want to silence your need - just want to make it clear how we currently think about this. Thanks.)