Drag and Drop Tables - Rails Backend (Part II)

In my last post I described how I used the react-beautiful-dnd package to make a drag-and-drop Semantic UI Table. By the end of the process, I was able to implement the DnD action to re-order my table's rows. The only problem was that the newly updated order didn't persist on the backend, so if the user refreshed the page, the new order would be lost. Not a good UX!

So the challenge for this week was to find a way to maintain the new order on the backend so that every time the user refreshes the page, it looks exactly like they'd expect.

Introducing the Problem

I recommend looking over last week's post to understand the use case of the React DnD table in my website, EffectiveDonate. I created a button under the "Starred Projects" table to save the user's new order of starred projects once they are done dragging and dropping. Upon clicking this button, I had a skeleton method called saveOrder() which would POST the new order to my API endpoint. The task for this week was to actually implement that route on the backend and ensure that every time the "Starred Projects" table populates, it fills in with the correctly ordered projects.

Table with the "Save New Order" button (to be implemented)

Database Changes

Before I could begin implementing the new route on my Rails backend, I had to think about the changes I needed to make to my database's user_starred_projects table. This table is a join table that only had two attributes: user_id and project_id. Previously when a user wanted to add a new project to their stars, it would be created with these two attributes and the next sequential id primary key. When the user got to their profile page, their projects would be displayed in reverse-chronological order (ascending id).

In order to allow the starred projects table to be dynamically ordered, I needed to create a new integer attribute in the user_starred_projects table I called order_number. I created a migration: rails generate migration add_order_number_to_user_starred_projects order_number:integer and then a rails db:migrate to migrate the database to the new schema.

Controller Changes

Now that the database was updated with the new order_number column, it was time to build out this attribute in the UserStarredProjects controller. I wanted to ensure that each new starred project was given the next sequential order number, so that the first star had an order_number of 1, and each additional starred project would increment that number. In the create controller action, I added the following code:

number_stars = UserStarredProject.where(user_id: @userStar.user_id).length
@userStar.order_number = number_stars + 1

I had an existing method in the UserStarredProjects controller to remove projects from the user's starred list, but since I had a new order_number attribute, I needed to consider the impact of removing a starred project on the rest of the list's orders. For example, if there were five projects in a user's starred list and the third one was removed, I would shift the following orders: (4 => 3, and 5 => 4). To scale this, I wrote the following code:

def remove_project
    user_id = params[:user_id]
    project_id = params[:project_id]

    @user_project = UserStarredProject.find_by(user_id: user_id, project_id: 
    project_id)
    higher_projects = UserStarredProject.where("order_number > ? AND user_id = 
    ?", @user_project.order_number, user_id)

    if @user_project.delete
      #Find all the projects with an order_number > @user_project.order_number 
      #and decrement them
      higher_projects.map do |project|
        project.order_number -= 1
        project.save
      end
      render json: {message: 'Removed Project' }, status: :accepted
    else
      render json: {error: 'Could Not Remove Project' }, status: :not_acceptable
    end
  end

Now that the removal of a starred project has been handled, the final controller action to implement was the endpoint that is called when the user re-orders their project list and saves the new order on the frontend. In the next section I will go into detail about how I POSTed the order on the frontend, but the order parameter gets sent as an array of the new project IDs (ex: [475, 170, 87]). So in the update_star_orders controller action, I iterate through this array, look up the UserStarredProject object that corresponds to its project_id and then assign it a new incrementing order_number:

def update_star_orders
    user_id = params[:user_id]
    order = 1
    project_array = params[:project_array]
    project_array.each do |id|
      user_star = UserStarredProject.find_by(user_id: user_id, project_id: id)
      user_star.order_number = order
      order += 1
      user_star.save
    end
    render json: {message: 'Updated Order' }, status: :accepted
end

Now the database will reflect the correct order_number for each starred project after the user rearranges their list!

Frontend Changes

Now that I had the update_star_orders endpoint implemented, it was time to write out the fetch action for the user to persist their new order. I first extract the project.ids from each project as an array, and send that array as a parameter to the update_star_orders endpoint I described in the previous section. Here's how I did it:

  saveOrder = () => {
    let { starredProjects } = this.state;
    let projectIds = []

    //go thru list of project objects and unshift their ids to the empty array 

    starredProjects.forEach(project => {
      projectIds.unshift(project.id)
    })

    // Take new state of starred project list and POST to endpoint

    const userId = localStorage.userid
    const url = `${config.get('API_URL')}/api/v1/update_star_orders`
    const headers = {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json'
        },
        body: JSON.stringify({user_id: userId, project_array: projectIds})
    }
    fetch(url, headers)
      .then(res=>res.json())
      .then(json => {
        if(!json.error){
          alert("New order saved!")
        }
      })
  }

The drag-and-drop reorder posted to backend

Conclusion

Overall, this exercise was a good way to get creative while thinking of a way to persist the list's order on the backend. This process reminded me of solving an algorithm, since there were limitless ways of tackling this problem and many different use cases that it had to meet. It would be an interesting exercise to see what other data structures I could use to optimize time/space efficiency here, and I'd love to explore refactoring this in different ways in the future. But for now, this solution worked and neatly maintains the order of each starred project in the database.

Thanks for reading!