Manual row-ordering (playlists, to-dos, etc.)

Feature Ideas
1

I’ve brought it up / asked before about sorting rows manually (i.e. drag & drop), but I want to bring it up again and see if there are any other options, and also to add this as a feature request (albeit very low priority).

What’s your idea?

The ability to sort rows in a table arbitrarily, i.e. by a drag & drop operations. This would work in the Data Viewer, and also provide some GraphQL API to order rows (move row x below row y, move row to top/bottom, etc.). Maybe it would be a feature you can toggle so the extra logic isn’t needed for every table.

What problem might it solve?

Records in a database are read in the same order inserted (depending on engine), but after that, there’s nothing you can do to change it except implement custom ordering. It’s very useful not having to introduce a sort order manually–finding the best solution, implementing it, the extra logic. In code, you simply use an “ordered dictionary”. Done. DB systems are dumb in this regard, while that is not their job, it’s an big inconvenience.

Have you seen it somewhere else?

In a perfect world, there wouldn’t be this mismatch between programming language data structures and database data structures. For example, in MS SQL there is the notion of hierarchical data (https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15), and when lots of data is naturally represented this way, it makes working with a DB so much easier. With programming data structures, it’s not even something you think about, you just do it.

Any ideas on how you think it could/should work?

Using arbitrary precision decimal numbers, and inserting “in between” such as adding 1.5 between 1.0 and 2.0, inserting 1.25 between 1.0 and 1.5. If you can’t have arbitrary precision, then some kind of normalization needs to be done. Another approach I’ve read is using 1…MAX_INT and using (position / count) * MAX_INT to space out the numbers so far that a conflict is rare… but can still happen in large data sets.

Other options are storing the sorting in another table, using “Allow Multiple”, and re-writing the field after sorting it. The problem with this is pagination–you can’t sort the data until it’s on the client. Another option is arbitrary length strings, in reverse order as you can insert ‘zz’, which will be ordered before ‘z’:

Previous post about ordering records:

2

Hey man! Thanks for this. Couple thoughts.

First off, this seems to be more of an application concern rather than a database/data management concern. It feels a bit to edge case to support natively in the 8base console UI, as it would require unique views for the sake of scoping relationships + the context that the records in a relationship require a specific order.

For example, Playlists and Songs. All the songs are in Songs table, and the Playlists are in playlist table. You’d first need to specify which playlist you want to order the songs in before then dragging and dropping them in a specific order. Without the context of playlist, you wouldn’t be able to order records using the records own table.

One thing that we’re working on is atomic operations. Things like auto increment. This way, you could set it up to where a positional field is auto incremented when records are added, and build some logic around that.

3

Hi Sebastian, yes this is definitely a higher-level feature. But on the other hand, “Allow Multiple”, “Custom Switch” aka enum, and “File” types for example are features on top of the database management, not really a core feature.

I just know that ordering things in a DB has ALWAYS been a challenge, and there are several ways to implement it. Maybe 8base can provide just a guide, a link or a custom library that makes it easier. In my current prototype, almost everything is implemented except row ordering. Anyway, something to think about.

Btw, I didn’t learn about Custom Switch (enum) until I watched a few videos. I assumed it only supported binary on/off data for a long time. If it was type “Enumeration”, or had formats like “Yes / No / Maybe” with more than two choices maybe I would have realized it’s an enum. Maybe a very short description of the type in the field creation header (where Type: Text) is or in the field section now would help.

Mike

4

I’m curious if there is any update to this thread. Sorting rows manually is a significant part of UIs, and there are several ways to do it, but no one recommended solution. It may be a higher-level feature, but for me it’s a requirement. For example sorting playlists, todos, favorites, etc.

Has anyone implemented or used a library to sort rows manually, for instance drag-and-drop reordering. For small sets of data, each record could be updated to reflect the new order, or you can save the ordering in JSON. I like the floating point implementation, but it requires unlimited precision, which is limited to 30 in 8base numbers.

5

Hey @mike_ekim1024 - yeah we haven’t been tackling this one as we feel it’s very specific to special usecases.

That said, we made an update that might be helpful in implementing it, which is update many through a relationship…

mutation {
  playlistUpdate(data: {
    id: "ckebqz1j4007v07l82c1fgp74", 
    songs: {
      update: [
        {
          data: {
          	id: "ckebr0gtt00fr07l77pe70ezy",
            position: 1
          }
        }, 
        {
          data: {
            id: "ckebr0gxk00ft07l72fktduyk"
            position: 2
          }
        }, 
        {
          data: {
            id: "ckebr0gyt00fv07l76ejz989f"
            position: 3
          }
        }
      ]
    }
  }) {
    id
    songs {
      items {
        id
      }
    }
  }
}

The update through an update would allow you to in one call update the dependent records. It would be up to your frontend to supply the appropriate index values, and you could even have a before trigger that validates the length and uniqueness of the update being sent.

6

This does the trick for short lists, definitely, thanks. I was just curious what other people do for continuously growing lists (todos/tasks for example). Maybe a different approach could be to partition the sorting, somehow. Another approach is literally implementing a linked list. The downside is that you loose server side sorting. This wouldn’t work well, for infinite scrolling, but it could for viewing slices of data (incomplete tasks).

https://theodorejb.me/2015/05/17/sql-linked-lists