Watermelondb: Support limit on query

[ieschalier]

What do you think about add limit condition to query ?

If application have huge amount of data (like 100 000 records for one table), it could prevent a too much memory usage and loading time.

Proposal

commentCollection
  .query(Q.where('likes', Q.gt(Q.column('dislikes'))))
  .limit(20000)

[radex]

This is a harder problem than it seems

I think this is incompatible with observability without native sorting, which is very difficult because of this issue: https://github.com/Nozbe/WatermelonDB/issues/15#issuecomment-419710051

I think the right approach to handle limiting in Watermelon might be to do chunking based on some field, e.g:

  .query(
    Q.where('likes', Q.gt(Q.column('dislikes'))),
    Q.where('position', Q.gte(0)),
    Q.where('position', Q.lt(100)),
  )

[stale[bot]]

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

[Kenneth-KT]

This is a harder problem than it seems

I think this is incompatible with observability without native sorting, which is very difficult because of this issue: #15 (comment)

I think the right approach to handle limiting in Watermelon might be to do chunking based on some field, e.g:

  .query(
    Q.where('likes', Q.gt(Q.column('dislikes'))),
    Q.where('position', Q.gte(0)),
    Q.where('position', Q.lt(100)),
  )

@radex Thanks for explaining and your suggestions, but I think the workaround suggestion does not quite work for large list in offline first apps.

Let say I want to limit the list to only show first 5 records in a large table, and I have the list of records pre-filled with sequential position during sync. Using your workaround, I can query first 5 records with position >= 0 AND position < 5 condition.

record #1, position: 0
record #2, position: 1
record #3, position: 2
record #4, position: 3
record #5, position: 4

But now if the user deletes record #3, the same query will just return 4 records:

record #1, position: 0
record #2, position: 1
record #4, position: 3
record #5, position: 4

Unless we run additional raw SQL query to manually shift the position number up for each record like:
UPDATE position SET position = position - 1 WHERE position > 2
But that would be a big performance hit for having to run such big updates for each record delete.
The cost for implementing such workaround with that bad performance is just not worth it.

I understand supporting LIMIT and ORDER BY queries may requires some sacrifice on observability, for my use case I am willing to sacrifice some observability to exchange for this performance gain.
However, I don't quite see how certain observability feature in 🍉 especially hard to support.
For example, I think that would be okay for single record observability (ie. monitoring changes of data fields in single record), queries result set without limit operation, etc. Even for queries that has limit applied to it, the worse case scenario we can still run the query in SQLite database.

(PS: Please point out if i got any concepts wrong)

Now we are planning to switch out from Realm, we really love the async nature of 🍉 as oppose to Realm's everything is synchronous which freezes the UI and also their performance of insertion is really terrible (more than 0.5 sec of UI freeze per record insertion) as our database size grows. It renders our app completely unusable during data sync. Our use case requires the database to be able to fetch few records with pagination from a list of > 100K records and able to search from it (may use FTS or roll out own tokenization indexer) without freezing the UI.

Please advise what I can help on that feature implementation or suggest workaround to use LIMIT query. Probably using raw queries? But then we would have to compose SQL without query builder, and also I am not sure if it is possible to observe that single record fetched with raw queries. The record set pagination feature is really a deal breaker for us to adopt 🍉.

[MuhammadUsman786786]

Why this issues is closed. Limit is very important features which is missed

[radex]

@MuhammadUsman786786 Please contribute! Some work has already been done: https://github.com/Nozbe/WatermelonDB/pull/622 and you can use raw SQL queries

[Unforgiven-wanda]

Does this mean that simple pagination is impossible as-is?

[radex]

limit is supported - please look at docs

[Unforgiven-wanda]

limit is supported - please look at docs

Only on Sqlite and RN, what about LokiJS and Web?
This seems kinda massive to omit out of the docs. Limit is the most basic of functionalities one would expect to find.

[radex]

This seems kinda massive to omit out of the docs

I checked, and the docs state that it's sqlite-only

https://nozbe.github.io/WatermelonDB/Query.html#sortby-take-skip

If you need limit with LokiJS, I encourage you to contribute this. I'm happy to point you in the right direction to get this done