Realm-java: findAllSorted and distinct

[ziggy42]

Goal

I'm using Kotlin and I'm trying to retrieve objects with findAllSorted using a Long that's a timestamp to sort them, and then filtering with distinct.

I've also asked on Stackoverflow

Expected Results

I expected to get the "newest objects for each distinct value of the field used by distinct

Actual Results

I got the oldest ones.

Steps & Code to Reproduce

override fun getStuff(): MutableList<Stuff> = realm.where(Stuff::class.java)
        .findAllSorted("timeStamp", Sort.DESCENDING)
        .distinct("otherId")

storage.saveStuff(Stuff("a", otherId=1, timeStamp=1))
storage.saveStuff(Stuff("b", otherId=1, timeStamp=2))
storage.saveStuff(Stuff("c", otherId=1, timeStamp=3))
// Other stuff with different otherId

storage.getStuff()[0].name == "c" // false

Version of Realm and tooling

Realm version(s): 1.2.0 / 1.1.1

Android Studio version: 2.2

Kotlin Version: 1.0.4

[kneth]

We have to investigate a bit i.e., write a little test.

[kneth]

@ziggy42 I have create #3522 which I believe is equivalent to what you describe. Indeed, I get the same unexpected behaviour.

[MahmoudAlyuDeen]

Any progress or ETA on this? if not, any work around to produce the wanted behavior?

[Zhuinden]

@MahmoudAlyuDeen distinct overrides the sort with Sort.ASC and currently there isn't really anything I can think of other than saving to another field as well where every time is stored as a long and the number is negated, that way the sort order is inverted as well. But that seems extremely tacky-hacky.

Still, this broke a while ago and that's the only workaround I can think of.

I saw an issue for it somewhere but I don't remember where I saw it

[ziggy42]

I'm using distinctBy from Kotlin's stdlib for now

override fun getConversations(): MutableList<PrivateMessage> = realm
            .where(PrivateMessage::class.java)
            .findAllSorted("timeStamp", Sort.DESCENDING)
            .distinctBy { x -> x.otherId }.toMutableList()

[MahmoudAlyuDeen]

@ziggy42 , unfortunately I'm stuck with Java for the time being, so I can't use that method I suppose.
Something weird happened though, I tried an old hack I learned when dealing with Firebase and their irreversible ascending only sorting, the hack is simply storing the negative of every timestamp. thus making the ascending sorting return the newest records first. however for some reason, distinct seems to ignore the negative values and still returns the smallest unsigned (the oldest record).

[Zhuinden]

I can think of other than saving to another field as well where every time is stored as a long and the number is negated, that way the sort order is inverted as well

---

...the hack is simply storing the negative of every timestamp.

Yup, same thing :tongue:

---

---

@MahmoudAlyuDeen did you change the distinct field to be the negative timestamp as well?

[MahmoudAlyuDeen]

@Zhuinden ,
Yep same thing.. I didn't see your comment when I posted mine.
Why would I want to negate the distinct field? In my case, the distinct field is an incremental id of another object, not a timestamp. Please explain further

[Zhuinden]

@MahmoudAlyuDeen I remembered https://github.com/realm/realm-java/pull/3522#issuecomment-250222976 and I think the distinct field has an implicit sort that overrides the one in findAllSorted, so it's actually that "incremental id" that should be mirrored to a negative value to fix your issue

I think it's on Hold because this will be fixed with the "results integration from the ObjectStore" that's on the way.

[kneth]

Yeah, work is done at the object store: https://github.com/realm/realm-object-store/pull/235

[Zhuinden]

Also needed by http://stackoverflow.com/questions/41666326/android-realm-query

[cmelchior]

@beeender Will this be fixed as part of https://github.com/realm/realm-java/pull/3834 as well?

[beeender]

Nope. Need core fix first https://github.com/realm/realm-core/issues/2332

[eygraber]

So core won't implement this unless there's enough interest over here?

[kneth]

@eygraber Both interest and use cases. When designing a new API or a new method, we appreciate the input from our users.

[eygraber]

@kneth gotcha.

My use case is that I have a Message which has a field chatId.

I want to get messages with a distinct chatId, but I want those messages to be the most recent messages for that chatId, so I'd like to be able to do something like:

realm.where(Message.class).findAllSorted("timestamp", Sort.DESCENDING).distinct("chatId")

[Zhuinden]

Ah so like how in Skype you can see the latest message sent for the given chat with a given user.

[ziggy42]

@eygraber that's my use case too

[zernyu]

Use case: Our app displays measurements gathered from IoT sensors. Our overview screen shows the latest measurement gathered per measurement type (pH, Salinity, Temperature, etc). So, similar to other use cases, I would like to be able to do:

realm.where(Measurement.class).findAllSorted("timestamp", Sort.DESCENDING).distinct("measurementTypeId")

[kneth]

@eygraber @ziggy42 @zernyu Thanks for the use cases.

[riyase]

For listing the latest messages from all users, same like Whatsapp
It will be great if something like this is there.
realm.where(Message.class).distinctAfterSort("userId", "timestamp", sortOrder).findAll();
or is there any existing method to do this?
and running distinct on RealmResults is deprecated now

[Zhuinden]

Apparently this stack overflow question would rely on this feature https://stackoverflow.com/questions/44344048/how-to-influence-realm-distinct-results/

But I see that the refinement is currently in review, so it'll happen soon!

[kneth]

Yes, as soon as we have it in core, we can implement it here.

[WhatDo]

One solution that worked for me could be to use

realm.where(Stuff::class.java)
  .findAllSorted("timeStamp", Sort.DESCENDING)
  .where()
  .distinct("otherId")

[Zhuinden]

Does it? I'd think it starts sorting by otherId instead of timeStamp

[WhatDo]

The following test passes

open class TestModel(
        var name: String = "a",
        @Index var time: Int = 0,
        @Index var type: String = ""
) : RealmObject()

realm.beginTransaction()
realm.insert(TestModel("a", 0, "test"))
realm.insert(TestModel("b", 1, "test"))
realm.insert(TestModel("c", 2, "test"))
realm.commitTransaction()

assert(stuff()[0].name == "c")

realm.beginTransaction()
realm.insert(TestModel("d", 3, "atest"))
realm.insert(TestModel("e", 4, "atest"))
realm.insert(TestModel("f", 5, "atest"))
realm.commitTransaction()

assert(stuff()[0].name == "f")
assert(stuff()[1].name == "c")

realm.beginTransaction()
realm.insert(TestModel("g", 6, "test"))
realm.insert(TestModel("h", 7, "atest"))
realm.insert(TestModel("i", 8, "test"))
realm.commitTransaction()

assert(stuff()[0].name == "i")
assert(stuff()[1].name == "h")


fun stuff() = realm.where<TestModel>(TestModel::class.java)
    .findAllSorted("time", Sort.DESCENDING)
    .where()
    .distinct("type")

[spookyuser]

Thank you so much @WhatDo that worked perfectly for me. Any updates on when we can do this natively?

[Zhuinden]

Fixed by https://github.com/realm/realm-java/pull/3522#issuecomment-328304653