Change MongoDB natural order, generate _id manually and sort by _id, insert rows between two rows implementation (React Data Grid )

Question

I am working on an Excel like webApp, and I want to implement a function to let user insert rows freely. I can easily modify frontend to visualize where to insert by splice and selected row id, but our backend is using mongoDB that generates _id and order by default. To solve this problem we generate before and after Ids and come up newId in between before and after.

if (this.state.insertIndex !== undefined && this.state.insertIndex !== 0 && this.state.insertIndex !== rows.length - 1) {
  const after = parseInt(rows[Math.max(this.state.insertIndex - 1, 0)]._id.toString().substring(0, 8), 16) * 1000;
  const before = parseInt(rows[Math.min(this.state.insertIndex + 1, rows.length - 1)]._id.toString().substring(0, 8), 16) * 1000;
  const timestampAsInteger = Math.floor(Math.random() * (after - before) + before);
  newId = Math.floor(timestampAsInteger / 1000).toString(16) + "0000000000000000";
}

Note: insertIndex initial undefined, setState when cell selected, and reset to undefined after row deletion

However, this algorithm seems to have many edge cases not considered, does anyone have experience to make it more robust? or any suggestion on edge cases?

Known Issue:

wrong behavior when select row[0]
generating dup _id (not knowing why)

Other ideas

Instead of generating _id, would it be easier to save the row state by localStorage render as the user see.
I found this enter link description here but not understand, could anyone explain if its a possible solution?

Any thoughts? Many thanks

`_id` is not default order ["How does MongoDB sort records when no sort order is specified?"](https://stackoverflow.com/a/11599283/2313887). That alone should point out that you are overthinking this. Believe me when I say that products like Excel have a **finite** "integer value" of allowed rows and columns. Store a separate value in your documents, since MongoDB **does not** sort on `_id` anyway. — Neil Lunn, Mar 17 '19 at 07:46
You are right I should have rephrase it because I found even I passed custom _id I still need to sort by _id at backend. But how I suppose to sync the order? — MJ Tsai, Mar 17 '19 at 07:57

score 0 · Accepted Answer · answered Mar 21 '19 at 20:26

After couple of days, I came over this work around solution. Instead of maintain a list of _ids, my methods has its limitations.

This is the code we generate our own mongo _id according to before and after timestamps

    let newId;
    let canInsert = true;
    if (this.state.insertIndex !== undefined) {
      if (this.state.insertIndex == 0 && rows.length >=2) {
        const before = parseInt(rows[Math.max(this.state.insertIndex, 0)]._id.toString().substring(0, 8), 16) * 1000
        const after = parseInt(rows[Math.min(this.state.insertIndex + 1, rows.length - 1)]._id.toString().substring(0, 8), 16) * 1000;
        if (after - before > 1000) {
          const timestampAsInteger = Math.floor(Math.random() * (after - before + 1) + before);
          if (timestampAsInteger - before > 1000) {
            newId = Math.floor(timestampAsInteger / 1000).toString(16) + "0000000000000000";
          } else {
            canInsert = false;
          }
        } else {
          canInsert = false;
        }
      } else if (this.state.insertIndex < rows.length - 1) {
        const before = parseInt(rows[Math.max(this.state.insertIndex, 0)]._id.toString().substring(0, 8), 16) * 1000
        const after = parseInt(rows[Math.min(this.state.insertIndex + 1, rows.length - 1)]._id.toString().substring(0, 8), 16) * 1000;
        if (after - before > 1000) {
          const timestampAsInteger = Math.floor(Math.random() * (after - before + 1) + before);
          if (timestampAsInteger - before > 1000) {
            newId = Math.floor(timestampAsInteger / 1000).toString(16) + "0000000000000000";
          } else {
            canInsert = false;
          }
        } else {
          canInsert = false;
        }
      }
    }

I understand above code is pretty hard to read, but there are many edge cases especially due to the newId generate method, we have limitation that two timestamp should at least have an interval > 1000, otherwise it will cause problem.

In fetch we need to check if the newId is generated otherwise we would add at the last row

      let index = this.state.insertIndex == undefined? Math.max(rows.length, 0): Math.max(this.state.insertIndex + 1, 0);
      json["value"] = index;
      rows = canInsert ? update(rows, {$splice: [[index, 0, json]]}): update(rows, { $push: [json] });
      if (canInsert) {
        this.showMessage("The project name cannot be empty");
      } else {
        this.showMessage("Impossible to insert, add last instead");
      }

At our back end, set _id to our newId

if (req.body.newId) {
    initialData['_id'] = req.body.newId;
}

The most difficult part of this feature is to catch so many possible edge cases. And there are limitation of insertion rows that two existed rows may not generate too closely. And there will be problems if the app keeps scaling up with more users. But it will work for smaller group of users and as an optional function when it is needed.

In the future if we can come up a more unique id generator that we may be able to insert without limitation. As far as I research no one ever came up something like this, but took me days to figure the things out. I hope this findings will save you days of doing research.

Change MongoDB natural order, generate _id manually and sort by _id, insert rows between two rows implementation (React Data Grid )

1 Answers1