Watermelondb: Native support for sorting

[sebastian-schlecht]

Hey,

it would be great to have native support for sorting according to a column (i.e. the timestamp) in order to avoid doing that in the JS thread.

Took a brief attempt to roll that on our end here - but I'm not sure about the API.

[radex]

Hey! Good first stab at the problem, but unfortunately, this is more difficult than it seems. Since Watermelon is observable, it's not enough for just the first query response to be sorted correctly. When a change occurs, you'd have to have some sort of a observable sort algorithm to insert a new item in the right place. At that point, it's easier to just do all the sorting in JS.

Are you hitting any performance bottlenecks with sorting? In our tests, sorting a reasonably sized array in JS is very fast. It could become measurably slow when you have a query that returns thousands of elements — but Watermelon doesn't solve that problem in general. (We don't have that use case, but I know many people do — it would be great to add to Watermelon or build on top of it some abstraction for chunking query responses)

[sebastian-schlecht]

Hm fair point.

Just to understand the underlying concepts (without having had a deep dive into the relevant code) - whenever a change occurs, the observed query is not executed again? Since sorting only changes the sequence of resulting items, not whether they are within the set or not, I thought one could simply re-execute the query that is being observed entirely. However, that wouldn't work ofc, if the query is not executed again.

[radex]

whenever a change occurs, the observed query is not executed again?

If you're making a complex query (with a JOIN, such as query comments where comment's post has post.blog_id = xxx), then the query will be re-executed on the database.

However, for simple queries, the observation is entirely JS-based, because it doesn't incur the latency penalty of waiting for the database again. The algorithm is roughly like this: listen to the collection that's queried, and when a record in that collection changes, check if the record matches the query. If it does: if record was on the rendered list, and was deleted — remove from rendered list. if it wasn't on the rendered list, and now matches — add to rendered list.

[febeling]

It could become measurably slow when you have a query that returns thousands of elements — but Watermelon doesn't solve that problem in general.

@radex What would be necessary to support this use-case of thousands of records (or let's say more than can be held im memory) - could you give a rough idea?

Honestly, I was surprised by this, because in my mind 'more data than fits the memory' is the point of a db.

[radex]

What would be necessary to support this use-case of thousands of records (or let's say more than can be held im memory) - could you give a rough idea?

you mean, to do sorting on thousands of records? I think it still might work in JS.

to have a great, general-purpose mechanism, we would need some way to ask the database to sort the results. that's easy (though limited) for SQLite, not sure about LokiJS. The problem comes with reactively observing changes. You could just ignore the problem by only fetching, but not observing queries that have sorting (or limiting). Or, you could sidestep it by re-fetching the query results on database change (that's how JOIN queries work).

This might work.

In the ideal world, we'd use some partial sorting algorithm to nicely observe queries with sorting.

I'm happy to discuss a plan to implement some version of this if you're willing to implement it

Honestly, I was surprised by this, because in my mind 'more data than fits the memory' is the point of a db.

It's very use case specific. For my use case (highly hierarchical data), you can have a ton of data, but never a ton of data in one view, so I just didn't have to implement this.

[febeling]

Honestly, I was surprised by this, because in my mind 'more data than fits the memory' is the point of a db.

It's very use case specific. For my use case (highly hierarchical data), you can have a ton of data, but never a ton of data in one view, so I just didn't have to implement this.

I can see how different use-cases don't have problems with this limitation. Databases are just versatile tools. Still, guaranteeing no collection ever becomes too large for memory feels risky to me, but it might be a valuable simplification (given a tech complexity budget).

Thanks for the offer regarding implementation; I'll think about it, but I don't want to dive right in.

[radex]

but it might be a valuable simplification (given a tech complexity budget).

exactly… obviously it would be best to have it, but for me, for now, it's a non-issue. I will re-open it, in case someone wants to pick this problem up, or at least make the first step

[stale[bot]]

Is this still relevant? If so, what is blocking it? Is there anything you can do to help move it forward?

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.