Realm-java: Impossible to chain sorting

[NinoDLC]

Describe your problem or use case

In SQL it is possible to "chain" queries to apply 2 ORDER BY or 2 GROUP BY on the same query. There's some use case in Realm where this is impossible.

If we have a collection of objects with a timestamp, and we want the 7 most recent (first .sort() used here) objects, from oldest to most recent (second sort).

It is described as such in SQL :

SELECT *
FROM (
   SELECT *
   FROM daily_mood
   ORDER BY timestamp DESC
   LIMIT 7
   ) t
ORDER BY timestamp

It would be described as such in Realm :

Realm.getDefaultInstance()
    .where(DailyMood.class)
    .sort("timestamp", Sort.DESCENDING) // Get most recents
    .limit(7) // Get 7 most recents
    .sort("timestamp") // Get 7 most recents starting from oldest one
    .findAll();

An exception arise : java.lang.IllegalStateException: A sorting order was already defined. It cannot be redefined.

Trying to Collection.reverse(RealmResults) throws obviously an UnsupportedOperationException.

The solution would be to copy all items in an ArrayList and reverse() it (or use a Set with a Comparator if your compareTo() / hashCode() include some unique properties) if but performance would suffer in some cases.

Describe the solution you'd like

Allow chain querying.

[cmelchior]

You can chain queries doing this instead:

Realm.getDefaultInstance()
    .where(DailyMood.class)
    .sort("timestamp", Sort.DESCENDING) // Get most recents
    .limit(7) // Get 7 most recents
    .findAll()
    .where() // Create a new query that only runs on the result of the first
    .sort("timestamp") // Get 7 most recents starting from oldest one
    .findAll();

[Zhuinden]

@cmelchior just for future-proofing, how do I do this in a partial sync realm?

[cmelchior]

The second query just sorts the results of the first query, so subscribing to the first should be enough.

This should work:

RealmResults<DailyMood> results = Realm.getDefaultInstance()
    .where(DailyMood.class)
    .sort("timestamp", Sort.DESCENDING) // Get most recents
    .limit(7) // Get 7 most recents
    .findAllAsync()
    .where() // Create a new query that only runs on the result of the first
    .sort("timestamp") // Get 7 most recents starting from oldest one
    .findAllAsync();
results.addChangeListener(...);

Subscribing when opening the Realm will also work:

SyncConfiguration config = new SyncConfiguration.Builder(user, url)
  .initialData(r -> {
    realm.where(DailyMood.class)
      .sort("timestamp", Sort.DESCENDING) 
      .limit(7)
      .subscribe()
  })
  .waitForInitialRemoteData(30, TimeUnit.SECONDS)
  .build();

// Run synchronous query
Realm realm = Realm.getInstance(config);
realm.where(DailyMood.class)
    .sort("timestamp", Sort.DESCENDING)
    .limit(7)
    .findAll()
    .where()
    .sort("timestamp")
    .findAll();

[NinoDLC]

Thanks a lot for the explainations, the doc wasn't very precise about this.

[realm-probot[bot]]

Hey - looks like you forgot to add a T:* label - could you please add one?