asked on

Persist changes in database - drag and drop reorder

Sorry for picking so many topics, but I think the logic is the same across all languages and databases.

I would like some advice on the best way to implement drag and drop which saves the order in the database. Currently I have the drag and drop working but it doesn't persist in the database yet.

The question I have at the moment is more around the logic required to do this properly. In the database, I have my normal unique ID and I have a 'position' column which is an auto increment integer field. However, when I drag and drop I only get back array index values which don't match the auto increment id in the database.

Basically like this:

import { sortableContainer, sortableElement } from "react-sortable-hoc";
import arrayMove from "array-move";

const SortableItem = sortableElement(({ value }) => <li>{value}</li>);

const SortableContainer = sortableContainer(({ children }) => {
  return <ul>{children}</ul>;
});

  const onSortEnd = ({ oldIndex, newIndex }) => {
    setItems((items) => arrayMove(items, oldIndex, newIndex));

    const position = oldIndex;
    const newPosition = newIndex;
    console.log(
      "the old index was " + position + " and the new is " + newPosition
    );
  };

// send http request to database and update order

Open in new window

Dave Baldwin

Databases like MYSQL and MS SQL do not have a fixed order like a spreadsheet does. Every query to a database must define the order you want for the items that are returned.

Scott Fell

I really have not dove into react but I can see how to do this. If you look at the example https://github.com/clauderic/react-sortable-hoc

class SortableComponent extends Component {
  state = {
    items: ['Item 1', 'Item 2', 'Item 3', 'Item 4', 'Item 5', 'Item 6'],
  };
  onSortEnd = ({oldIndex, newIndex}) => {
    this.setState(({items}) => ({
      items: arrayMove(items, oldIndex, newIndex),
    }));
  };
  render() {
    return <SortableList items={this.state.items} onSortEnd={this.onSortEnd} />;
  }
}

Open in new window

What looks like is happening is you have an array of items. If item 6 gets moved to the 2nd position you can use splice to create an updated array. Then save the array to the database in one field. Next time the page loads, pull from that field holding the array.

Crazy Horse

ASKER

Thanks Scott, I managed to get the contents of the new array by doing this (please note I changed this to a functional component so there is a change in syntax)

const onSortEnd = ({ oldIndex, newIndex }) => {


    setItems((items) => arrayMove(items, oldIndex, newIndex));


    const arrayTest = arrayMove(items, oldIndex, newIndex);


    async function makePostRequest() {
      const config = {
        method: "patch",
        url: "http://localhost:8000/api/admin/faq/order",
        headers: { Authorization: "Bearer " + auth.token },
        data: {
          order: arrayTest,
        },
      };


      let res = await axios(config, { order: arrayTest });


      console.log(res);
    }
    makePostRequest();
  };

Open in new window

arrayTest is the new array after the drag and drop is complete. I am then sending that whole array to the backend and I thought perhaps what I should do is loop over the array in the backend and update each record with the new position id. My concern was that if you have loads of records it probably isn't efficient to update every single record in the database. In my case I don't even have 10 records but that might not always be the case.

Crazy Horse

ASKER

Maybe showing some data will help. When the page loads the array looks like this:

[
  {
    _id: '5ed273049b268308302cb1fb',
    question: 'question 1',
    answer: 'answer 1',
    id: 1,
    __v: 0
  },
  {
    _id: '5ed273439b268308302cb1fd',
    question: 'question 2',
    answer: 'answer 2',
    id: 2,
    __v: 0
  },
  {
    _id: '5ed276129b268308302cb1ff',
    question: 'quesiton 3',
    answer: 'answer 3',
    id: 3,
    __v: 0
  }
]

Open in new window

Then after I drag and drop something the array I send to the backend looks like this:

[
  {
    _id: '5ed276129b268308302cb1ff',
    question: 'quesiton 3',
    answer: 'answer 3',
    id: 3,
    __v: 0
  },
  {
    _id: '5ed273049b268308302cb1fb',
    question: 'question 1',
    answer: 'answer 1',
    id: 1,
    __v: 0
  },
  {
    _id: '5ed273439b268308302cb1fd',
    question: 'question 2',
    answer: 'answer 2',
    id: 2,
    __v: 0
  }
]

Open in new window

So, all that has changed is the order of the array. Looping through the records in the backend doesn't help because the _id and the id are the same

Crazy Horse

ASKER

I actually have got it working by using the index value like below. It works but just wanted to check if the code looks okay or if I am doing something wrong / could do it better? This is the node.js code updating the database.

exports.faqSort = async (req, res) => {
  const { newOrder } = req.body;


  newOrder.map(async (o, index) => {
    // doing this because index starts at 0 and my database id's start at 1
    let orderkey = index + 1;
    await Faq.updateMany({ _id: o._id }, { $set: { id: orderkey } });
  });

Open in new window

Scott Fell

what I was suggesting is to store the json as text in one field. Each time the order is changed, send an Ajax call to the DB to store the reordered json as text.

Crazy Horse

ASKER

Sorry, not really following. Do you mean store the entire array in one record?

Scott Fell

Yes, exactly.

Save this as a text in one field.

I probably would just save the order though.

['question 3', 'question 1', 'question 2']

Open in new window

Then when you go to display the questions on the page, loop through the array and link on the "question 3" with the other data to display question 3, then 1 then 2. But you can just as easily save the entire json you just posted into one field as well.

Crazy Horse

ASKER

I know this might be tricky, but is there anyway you could show me the proposed database structure based on the screenshot I have here?

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial