Add sort functionality to tables

[mfisher87]

No description provided.

[hazelshapiro]

I added this to the MVP milestone, but I would say it is less important than enabling editing throughout @MattF-NSIDC

[Sherwin-14]

@mfisher87 Hey, I just wanted to ask is this still relevant? If yes, do you any specific opinions regarding this? I solved it using a JS library named sort-table. Is this solution plausible?

[mfisher87]

Hey @Sherwin-14 , sorry for the delay in responding. Had a medical emergency with one of my pets (they're recovering well now!).

I haven't used that library before, but perhaps! I'll have to think a little more. I'm also assigned to work on this project for the next couple of weeks, so I'll be more active in the immediate future.

My one thought right now is perhaps we will need to do pagination on the server side, so we might need to sort on the server side anyway. Eventually there may be thousands of Projects, and we probably only want to show ~20 at a time. That can probably wait until we have more data, though.

[Sherwin-14]

@mfisher87 I am so sorry to hear that, I hope they are doing fine ! I just wanted to ask you since I had thought about doing this and your suggestions for pagination is something that I too would ponder on.

[mfisher87]

He's doing really well now, but still has a week and a half of recovery before he'll be fully healed! I'm going to keep thinking about how we should implement this. cc @rmarow

[Sherwin-14]

Just for my curiosity, Is server side sorting something that most web applications do? I mean to say that I had thought of sort-table library as it can easily sort items on the client side. I am just asking this since I have little idea about how production grade applications usually approach situation like these ;)

[mfisher87]

It depends on the expected scale of the data! There are some pieces of data that we know won't get very big, e.g. the list of possible statuses a survey/project can have, and we will never need to paginate.

The list of all surveys/projects can get pretty big over the years, so I feel server-side pagination is going to lead to the best chance of good performance over the lifetime of the application. I really only reach for pagination for cases where I know the data is big or will grow fast. The SQL database that backs the application is extremely good at grabbing those smaller chunks.

[Sherwin-14]

@mfisher87 Hey, Is there any progress in this issue? I was thinking of some solutions for this and i guess an algorithm like bucket or radix sort might be useful here. Would like to know what you think about this ;)

[mfisher87]

We didn't make it to this issue in our last round of funding, so there was no progress unfortunately :(

In most cases I'd say it's really important to do sorting in the database, but the size of the data in these tables is going to grow at a rate of dozens per year, so perhaps we should take a short-term approach to this problem with a plan to come back and implement a long-term approach later?

Short term approach could be as simple as using an out-of-the-box library to do the sorting in JS (DataTables? - this tutorial looks really helpful).

I think this would be the best place to start, and then we can create a separate issue for the long-term approach. What do you think?

---

A stab at long-term "best practice" approach:

• The user clicks the table header to sort by that field. The default is descending.
• The page reloads with a new query parameter sort={field_name}.
• Flask receives this sort argument and uses it in the db query as order_by(selected_field.desc()) that field.
• The Jinja template receives the sorted results and displays them, and an ⬇️ indicator appears next to the field that is sorted in descending order.
• The user clicks the same table header a second time, it switches to ascending order.
• Query parameter sort={field_name} asc is passed (or sort=-{field_name} to make it easier to parse?)
• Flask receives the sort argument and recognizes the indicator that this should be an ascending sort
• The database query does order_by(selected_field.asc()) instead this time
• The template indicates ascending order with a ⬆️ icon

The flask-table library is very outdated but may provide useful code for generating the column header behaviors.

Finally, it would be great to think about pagination at this point. Flask-SQLAlchemy has pagination features out of the box that we could leverage. Again, data growth will be slow, but it would be great to set the app up to be low maintenance over the long term when the data does get too big to reasonably show on one page.

All of this is just thinking out loud, please share your ideas to make it better :) Especially, "should the sort order default to ascending or descending?" For dates, I think descending is preferable, but for alphabetical columns, ascending is preferable. We could allow the user to choose ascending or descending by displaying two buttons for each column. What do you think?

cc @rmarow

[Sherwin-14]

Well all the suggestions seem appropriate to me . Although the short-term approach was something similar to what I suggested the only difference was that I did this with sort-table JS . Datatables library also sort's the table on the client side so I guess if that is the way we decide to move forward, then using either of them would suffice. I have done this already using sort-table so you can check if its ok or not;)

One thing that we can do is use checkboxes instead of buttons? if it doesn't interfere with the design then I think this maybe a good idea.

[mfisher87]

have done this already using sort-table so you can check if its ok or not;)

Where can I find this? I may not have time for an in-depth review in the short-term, though.

One thing that we can do is use checkboxes instead of buttons? if it doesn't interfere with the design then I think this maybe a good idea.

Are you imagining being able to sort by multiple columns? It couldn't hurt, but I don't think it will be required (yet).

[Sherwin-14]

@mfisher87 Yes I was talking about using multiple columns to sort. I'll fix this and let you know;)

[Sherwin-14]

I had been working on this and it seems like a lot of changes have been made ;). I just wanted some clarity over table that we are thinking of applying this sorting functionality. Maybe in this case sort-table will not be a good option. And as far as I could remember, this sorting functionality was required for the profile page if I am not wrong.

[mfisher87]

Hey @Sherwin-14 yeah, we had a pretty active work period recently! Anything we can do to explain changes that might help?

I think the most important place to add sorting is the Assessments table. @hazelshapiro can you confirm?

[hazelshapiro]

I think the assessments table is a good place to start, but the object library will also need sort/filter functions since we could easily get to 100s of objects @mfisher87 @Sherwin-14

[mfisher87]

Good point. Since the object library table is likely to get big long before the assessments table, I think that might be an even better place to start.