Range operator or OFFSET/LIMIT

[astigsen]

Allow you to filter TableViews and LinkLists by range (e.g. first 5 item, last 10 items or from item 5 to 10).

The `limit' operator would be a subset of this functionality.

[kneth]

See https://github.com/realm/realm-java/issues/544 for inspiration

[simonask]

What's the use case that makes this relevant in Core? This might sound provocative, but think about it: OFFSET/LIMIT is only useful when the data has been sorted, but if we have to sort the data, there is no performance benefit to limiting the data on Core's side of things.

In my mind it would make no practical difference if this was implemented in the bindings exclusively — however, it might be more convenient to do it on TableView, which is also a valid reason to do it. :-)

[astigsen]

The specific use case came from @cmelchior trying to do queries that only used a range and then did an aggregate on it (the UI continuously showing the sum of the last 7 entries in a list). So for it to be possible to use the aggregate operators it has to be in TableView.

Another reason to put it in core, is that it would have to be part of the general query pipeline for the TableViews to update correctly as the underlying data changes).

OFFSET/LIMIT is only useful when the data has been sorted

or on LinkList's which are always ordered,

[cmelchior]

Cocoa issue about the same thing here: https://github.com/realm/realm-cocoa/issues/1580

[rrrlasse]

I've put this in P2 because I think that we're currently too busy fixing hard bugs.

[cmelchior]

Any chance this could get bumped to P1 as I assume it would be fairly trivial? This question keeps popping up for Realm Java + there might actually be performance concerns for very large RealmResults: https://github.com/realm/realm-java/issues/3685

[cmelchior]

It seems this is going to be implemented as part of the support for Partial Sync. In that case we probably need to consider the case of "Get last X records".

The SQLite way of doing that would be:

SELECT * FROM mytable LIMIT 10 OFFSET (SELECT COUNT(*) FROM mytable)-10;

[simonask]

That looks slightly crazy. The much more conventional way to do it is to SORT BY x DESC LIMIT n, and then sort differently client-side. :)

Note that LIMIT without SORT is always useless in Realm because objects do not have any intrinsic order.

[astigsen]

Note that LIMIT without SORT is always useless in Realm because objects do not have any intrinsic order.

Except in lists. It would actually be quite useful to be able to define a range (like the last 10) from a list that you want synced.

[cmelchior]

That looks slightly crazy. The much more conventional way to do it is to SORT BY x DESC LIMIT n, and then sort differently client-side. :

Which fits really poorly with our lazy-loading paradigm.

It doesn't appear that something like SELECT LAST X is supported in any SQL variant I could find. This also means having something like that would be a great feature compared to using SQLite. Especially when we consider auto-updating query results.

[nirinchev]

Why would it fit poorly with lazy-loading?

[kneth]

Except in lists. It would actually be quite useful to be able to define a range (like the last 10) from a list that you want synced.

But you would do that by specify a sorting order combined with a length condition in the partial sync query, right?

[rrrlasse]

@cmelchior many SQL engines have internal result set caches, so if you do "LIMIT n", it will not need to re-run the entire query when you request the next pages

[cmelchior]

@nirinchev In Java, it would e.g mean allocating every object on the Java side (which is expensive), unless we find an API for doing it on the C++ side. But that aside, our API's are built around making things easier for users, which this falls very much inside of.

[nirinchev]

@cmelchior I think Simon's idea was to send a query like "give me top 10 items ordered by date desc" and then when it completes, do a "give me all items ordered by date from the result list". This would still be executed in core although would require more explicit control over subscriptions.