Firestore query to sort posts by time and score

Question

I'm trying to sort out trending posts by sorting them by time and karma using the following code:

FirebaseFirestore.getInstance().collection("topics")
    .orderBy("time", com.google.firebase.firestore.Query.Direction.DESCENDING)
    .whereGreaterThanOrEqualTo("time",(System.currentTimeMillis() % 1000)-1000*60*60*24*7)
    .orderBy("karma", com.google.firebase.firestore.Query.Direction.DESCENDING).startAfter(currentDoc).limit(10).get()
                .addOnSuccessListener(new OnSuccessListener<QuerySnapshot>() {
                    @Override
                    public void onSuccess(QuerySnapshot documentSnapshots) {
                        for (DocumentSnapshot d : documentSnapshots.getDocuments()){
                            Topic topic = new Topic();
                            topic.setId(d.getId());
                            topic.setUsername(d.getString("username"));
                            topic.setCaption(d.getString("caption"));
                            topic.setPic(d.getString("pic"));
                            topic.setTime(d.getLong("time"));
                            topic.setType(d.getString("type"));
                            topic.setImage(d.getString("image"));
                            topics.add(topic);
                            if (topics.size()==documentSnapshots.size()){
                                currentDoc = d;
                                adapter.updateList(topics);
                                loading.setVisibility(View.GONE);
                            }
                        }

                    }
                }).addOnFailureListener(new OnFailureListener() {
            @Override
            public void onFailure(@NonNull Exception e) {
                e.printStackTrace();
            }
        });

I added the Index in Firestore. But it's not working.

I mean it's showing 7 days old posts. But it's not sorted by karma. It's showing 7 days old posts sorted by time. And if i remove the orderBy("time") from the query. It's crashing.

Need Help :(

if there is a crash there is always always a crash report. You can share log for other to get the idea. — Shubham AgaRwal, Jan 28 '18 at 08:41

Alex Mamo · Answer 1 · 2018-08-27T08:36:37.353

4

The ordering of the query terms is important. To solve this, change the order like this:

FirebaseFirestore.getInstance().collection("topics")
    .orderBy("karma", com.google.firebase.firestore.Query.Direction.DESCENDING)
    .orderBy("time", com.google.firebase.firestore.Query.Direction.DESCENDING)
    .startAfter(currentDoc).limit(10).get().addOnSuccessListener(/* ... */);

This will work as long as you are using an index.

As you can see, I have removed from the query the following line of code:

.whereGreaterThanOrEqualTo("time",(System.currentTimeMillis() % 1000)-1000*60*60*24*7)

Because according to the official documentation regarding Order and limit data with Cloud Firestore you cannot:

Range filter and first orderBy on different fields.

Another approach would be to use:

FirebaseFirestore.getInstance().collection("topics")
    .orderBy("time", com.google.firebase.firestore.Query.Direction.DESCENDING)
    .whereGreaterThanOrEqualTo("time",(System.currentTimeMillis() % 1000)-1000*60*60*24*7)
    .startAfter(currentDoc).limit(10).get().addOnSuccessListener(/* ... */);

And as you can see, both orderBy and whereGreaterThanOrEqualTo are used on the same property.

edited Aug 27 '18 at 08:36

answered Jan 28 '18 at 09:24

Alex Mamo

130,605
17
163
193

I'm curious to know if this works as well because it seems Firebase documentation states you cannot do an `orderBy()` paired with a `where()` statement using a different value. I will give this a try and report back. – AdamHurwitz Aug 25 '18 at 22:18
1

As suspected this does not work. I attempted the following: `return FirestoreCollections.contentCollection .collection(ALL_COLLECTION) .orderBy(QUALITY_SCORE, DESCENDING) .orderBy(TIMESTAMP, DESCENDING) .whereGreaterThanOrEqualTo(TIMESTAMP, getTimeframe(WEEK))`. Error: **Invalid query. You have an inequality where filter (whereLessThan(), whereGreaterThan(), etc.) on field 'timestamp' and so you must also have 'timestamp' as your first orderBy() field, but your first orderBy() is currently on field 'qualityScore' instead.** – AdamHurwitz Aug 26 '18 at 01:12
1

@AdamHurwitz Thank you for commenting on my answer. Yes, you're right. By the time I was answering this question, I missed the fact that you mentioned in your comment. Just updated my answer. – Alex Mamo Aug 27 '18 at 08:46
1

@AdamHurwitz Hi Adam! Have you seen my updated answer? – Alex Mamo Aug 27 '18 at 13:35
Yes, I have updated the vote with an up vote :-). Would you be able to provide guidance on how I can transform a Collection such as a List into a `Query`? Since it does not seem feasible to query for everything larger than a specified `TIMESTAMP` and sort by `QUALIY_SCORE` I'm filtering a query by a `TIMESTAMP` attribute, and then sorting on the client side by `QUALITY_SCORE`. In order to use `FirestorePagingOptions.Builder`'s **setQuery()** method I must pass in a `Query` object. – AdamHurwitz Aug 27 '18 at 19:45
@AdamHurwitz Thanks for answering back. I don't understand what do you mean through `transform a Collection such as a List into a Query`. We are usually using a query to get the data from a Collection and add it to a list not the opposite. As far as I know you cannot transform a list into a query object or maybe I didn't undesrstood it so well. Why are you affraid of using two filtering method? If you know that the returning data set is also very large, you can use whereGreaterThan and whereLessThen methods. – Alex Mamo Aug 27 '18 at 20:15
@AdamHurwitz Because this sounds as a new question (which is not related with the initial) I suggest you post another fresh one, so me and other users can help you. Cheers! – Alex Mamo Aug 27 '18 at 20:15
I would love to be able to use two filtering methods, but as indicated by the Firebase Engineer "Frank van Puffelen" in this post that is not possible. https://stackoverflow.com/questions/52031134/firestore-composite-index-sort-not-modifying-order/52040896?noredirect=1#comment91044697_52040896 – AdamHurwitz Aug 27 '18 at 21:36
I'm not concerned with the size of the data set. I need to apply a 2nd layer of ordering after the query is complete on the client side since I cannot filter by a first attribute and sort by a second attribute. The client side ordering is straightforward, however the `FirestorePagingOptions.Builder` `setQuery()` method only accepts a **Query** as a parameter, and not a list which I would end up with if ordering on the client side. – AdamHurwitz Aug 27 '18 at 21:40
This issue relates directly to the original question of sorting by 2 attributes. See above: _"trending posts by sorting them by time and karma"_. I will take your advice and create a new post to discuss building out a `PagedListAdapter` and creating a custom `DataSource` for the Firebase queries in order to solve this problem. https://developer.android.com/topic/libraries/architecture/paging/data#custom-data-source – AdamHurwitz Aug 27 '18 at 21:43
@AdamHurwitz I've read an interesting [answer](https://stackoverflow.com/questions/51859652/how-to-exclude-an-element-from-a-firestore-query/51973447#51973447) a few hours ago, it might help you. And if you are interestd in real-time updates, **[this](https://stackoverflow.com/questions/50741958/how-to-paginate-firestore-with-android)** is a recommended way in which you can paginate queries by combining query cursors with the limit() method. I also recommend you take a look at this **[video](https://www.youtube.com/watch?v=KdgKvLll07s)** for a better understanding. – Alex Mamo Aug 27 '18 at 22:05
Thank you for the research and resources! The first solution of running 2 separate queries is interesting, but would add more cost in the long run from double the querying. I got my swipe to refresh feed working with the PagedListAdapter using the documentation from my last comment and following this CodePath. https://github.com/codepath/android_guides/wiki/Paging-Library-Guide. I posted a **DataSource** code sample in my answer here if you'd like to see. https://stackoverflow.com/questions/51859652/how-to-exclude-an-element-from-a-firestore-query/52048159#52048159. – AdamHurwitz Aug 27 '18 at 23:34
@AdamHurwitz You're welcome Adam! Interesting approach, I'll take a closer look, thanks! – Alex Mamo Aug 28 '18 at 08:00
1

My solution was marked as a duplicate unfortunately. However, you can check out the solution I outlined here: https://stackoverflow.com/questions/51859652/how-to-exclude-an-element-from-a-firestore-query/51973447?noredirect=1#comment91057976_51973447. – AdamHurwitz Aug 28 '18 at 17:18

Firestore query to sort posts by time and score

1 Answers1