A question about ordering nested associations by the end user

I have Clients and Optics, each client has one / many optics. Here’s the data inside the optics table:

postgres

All here belong to client with id = 8.

Let’s say the client wants to re-order this list making the row with r_sph = 2.5 (appear) first on the client side. What is the best approach to do this? How to save the custom client based ordering / sorting in the database, making that row appear first when he reloads the page?

Should I add a delta (integer) field to the table to save the ordering (1, 2, 3), and manipulate the delta value when he drags / drops / order on the client side? Or is there a simpler solution? Thank you.

1 Like

I would probably store the column names by which to sort in user preferences.

userPreferences = {
  // ...
  optics: {
    // ...
    orderBy: ["r_axs", "r_sph", "id"]
    // ...
  }
  // ...
}

If it’s a web app, then either cookie or local storage would do. Or you can store it on the backend somewhere.

1 Like

That can be a solution for that particular need. I guess if I want to save users ordering (which can be random and not related to column preferences), then I need to add an additional integer field to the table to save their ordering by 1, 2, 3 etc… for each row and then display the list by that ordering coulmn.

1 Like

Yeah I would go with a simple integer to store the order. It is a little expensive to change the order since you’ll have to touch a number of rows when changing the order but it is a very simple and straightforward solution.

2 Likes