How to change the order of a document in a list, MongoDB

Question

I have a list of cards with the field "position" to save the index of the new card, starting with: 1,2,3,4,5.

Cards list Docs :

{ "_id" : ObjectId("5e7e117de6f50cbda72ff41a"), "name" : "stark", "position" : 1,"idBoard":555 }
{ "_id" : ObjectId("5e7e117de6f50cbda72ff41b"), "name" : "sinatra", "position" : 2,"idBoard":555 }
{ "_id" : ObjectId("5e7e117de6f50cbda72ff41c"), "name" : "muzan  rusbé", "position" : 3,"idBoard":555 }
{ "_id" : ObjectId("5e7e117de6f50cbda72ff41d"), "name" : "shakira", "position" : 4,"idBoard":555 }
{ "_id" : ObjectId("5e7e117de6f50cbda72ff41e"), "name" : "john travolta", "position" : 5,"idBoard":555 }

I want to insert the name stark with position 1 in position 5.

Result :

sinatra - position 1,
muzan - position 2,
shakira - posiiton 3,
john travolta - position 4,
stark - position 5 (new position)

Is there a way to do this? It's like removing a value from the X index and adding in the Z index to an array. I want change the positon of each card.

Edit: IdBoard 555 is the Board ID that each card has been saved to. Api /card/{Board ID 555}/ save the card with idBoard is 555.

what is your MongoDB version ? Is that something like position for all lowest valued docs where `"idBoard":555` changes if one doc's position changes ? — whoami - fakeFaceTrueSoul, Mar 27 '20 at 18:13
version 4.2.3, idBoard is the ID of the Board that the card was saved. When find by Board id 555 he do find all cards with idBoard is 555. — Braian Silva, Mar 27 '20 at 18:24
So this position change has to happen for cards of `idBoard` where the changed card belong to, correct ? — whoami - fakeFaceTrueSoul, Mar 27 '20 at 18:25
Yes. Reordering every card. It is like the trello card, where it is possible to rearrange the position. — Braian Silva, Mar 27 '20 at 18:28
For this to happen, you need to have current position of `stark`, would be able to have it before update operation ? — whoami - fakeFaceTrueSoul, Mar 27 '20 at 18:49

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-03-27T20:27:13.200

1

If you can get current position of given person, rest updates can be done in one call :

Step 1 : Get current position of player

db.getCollection('cards').findOne({name : 'stark', idBoard: 555}, {position :1, _id :0})

Step 2 : Reduce position of all other players greater than current position of given player & less than or equal to passed-in new position.

/** current position : 1, new position : 5 */
db.getCollection("cards").bulkWrite([
  {
    updateMany: { // This updateMany works if `currentPositionValue` < `newPositionValue`
      filter: {
        /** As many filters as possible in order to operate on less docs */
        idBoard: 555.0,
        name: { $ne: "stark" },
        $expr: { $and: [{ $gt: ["$position", 1] }, { $lte: ["$position", 5] }] }
      },
      update: [{ $set: { position: { $subtract: ["$position", 1] } } }]
    }
  },
  {
    updateOne: {
      filter: { name: "stark", idBoard: 555.0 },
      update: { $set: { position: 5 } }
    }
  }
]);

Note : Here we're passing idBoard: 555 in order to avoid cross matching of docs, Ex. : If same player stark exist in two boards, we need to be sure on which board we need to update the positions.

Update :

You might need to switch your updateMany of .bulkWrite() based on requirement, below is when your currentPositionValue > newPositionValue

/** current position : 4, new position : 1 */
{
    updateMany: {
      filter: {
        idBoard: 555.0, name : {$ne : 'john travolta'},
        $expr : {$and: [{ $gte: ["$position", 1] }, { $lt: ["$position", 4] }]}
      },
      update: [{ $set: { position: { $add: ["$position", 1] } } }]
    }
  }

So do a check & switch between these two options.

edited Mar 27 '20 at 20:27

answered Mar 27 '20 at 19:05

whoami - fakeFaceTrueSoul

17,086
6
32
46

It worked really well. If I want to put position 4 in position 1 will this solution be the same? use $ gt and $ let? – Braian Silva Mar 27 '20 at 20:16
@BraianSilva : Ohh I've missed that part :-) ,Due to complexity you might not be able to do it in one query but with two queries & a switch condition you can do your things very well(Concept should be same), Updated my answer check it. – whoami - fakeFaceTrueSoul Mar 27 '20 at 20:27
Do you know Trello? I'm trying to reorder like him with cards and lists, add to the top, bottom and middle. We can talk more about this? – Braian Silva Mar 27 '20 at 21:01
@BraianSilva : I've heard of Trello, but not sure how exactly it's being done, but with MongoDB update queries this kind of scenario you might need to do it in code/ half on half like what we're doing here :-) It's certainly is interesting, please post it here if you find anything better :-) – whoami - fakeFaceTrueSoul Mar 27 '20 at 21:06
Each position starts with the value 65,535. **bottom of list** - maximum pos currently in the list + 65553 **top of list** - minimum pos currently in the list divided by two. **middle of list** - average of pos of the two adjacent items. The problem is that, to the left of the middle he makes the average and to the right of the middle he makes another average, this is the most difficult part. How to do it? – Braian Silva Mar 27 '20 at 21:21
@BraianSilva: A bit hard to understand, Can you raise a new question myself or someone can give you best way to do :-) – whoami - fakeFaceTrueSoul Mar 28 '20 at 00:30
1

Done. Thanks. [How to rearrange cards and lists like Trello?](https://stackoverflow.com/questions/60896229/how-to-rearrange-cards-and-lists-like-trello) – Braian Silva Mar 28 '20 at 01:26
1

@BraianSilva : Just saw :-) will definitely try to answer :-) – whoami - fakeFaceTrueSoul Mar 28 '20 at 01:26
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/210472/discussion-between-braian-silva-and-whoami). – Braian Silva Mar 28 '20 at 01:37

How to change the order of a document in a list, MongoDB

1 Answers1