How to workaround firestore basic query in my scenario

Question

I've a list of documents which looks like below structure.

Posts ->
  ->uuid_1
    -> approvedCount : 5
    -> postMsg : "Hello world 1"
    -> Timestamp : 1234567890
    -> userId : "user1"
  ->uuid_2
    -> approvedCount : 6
    -> postMsg : "Hello world 2"
    -> Timestamp : 1234567891
    -> userId : "user2"

There may be thousands of the posts in firestore and the size is ever increasing.

condition -> approvedCount must be greater then 5 when filtering in query.

Now I want to fetch 20 records every day in ascending order of the timestamp. I don't want to get duplicate so lets say today I fetched 20 first records in ascending order now tomorrow I want to fetch next 20 and so on..

I tried something like

FirebaseUtil.getFireStoreDB("Posts").whereGreaterThan("approvedCount", 5)
                .limit(20)
                .orderBy("Timestamp", Query.Direction.ASCENDING)
                .get()

But don't get the idea how I'll fetch next posts everyday. Because from the docs I got to know you can't query 2 fields with < or > otherwise it will be easier with timestamp and approvedCount

Alex Mamo · Answer 1 · 2018-05-30T21:39:40.010

2

To solve this, you need to use Paginate Data with Query Cursors in which you'll find a very useful method named startAfter() in which you can pass as an argument the last visible element from the documentSnapshots object. As in the official documentation, you should use the following code:

FirebaseFirestore rootRef = FirebaseFirestore.getInstance();
// Construct query for first 20
Query first = rootRef.whereGreaterThan("approvedCount", 5)
                .orderBy("Timestamp", Query.Direction.ASCENDING)
                .limit(20);

first.get().addOnSuccessListener(new OnSuccessListener<QuerySnapshot>() {
    @Override
    public void onSuccess(QuerySnapshot documentSnapshots) {
        // Get the last visible document
        DocumentSnapshot lastVisible = documentSnapshots.getDocuments().get(documentSnapshots.size() - 1);

        // Construct a new query starting at this document, to get the next 20 records
        Query next = rootRef.whereGreaterThan("approvedCount", 5)
                .orderBy("Timestamp", Query.Direction.ASCENDING)
                .startAfter(lastVisible)
                .limit(20);

        // Do what you need to do with your query
    }
});

edited May 30 '18 at 21:39

answered May 30 '18 at 18:56

Alex Mamo

130,605
17
163
193

Ok John, keep me posted. Cheers! – Alex Mamo May 30 '18 at 19:24
I am getting an error `You have an inequality where filter (whereLessThan(), whereGreaterThan(), etc.) on field 'approvedCount' and so you must also have 'approvedCount' as your first orderBy() field, but your first orderBy() is currently on field 'timeStamp' instead.` I tried different combination but the results are not what I wanted. I simply want `select * from posts where approvedCount > 5 orderBy Timestamp asc limit 20` – Sunny May 30 '18 at 21:05
Oh, you're right, order in Firestore is very important. Just edited my answer. If you have a filter with a range comparison (<, <=, >, >=), your first ordering must be on the same field. Also don't forget to use [indexes](https://firebase.google.com/docs/firestore/query-data/indexing). – Alex Mamo May 30 '18 at 21:39
I've not tried it. I'll try it tonight. but what I think is I can have two collections `all_posts` and `approved_posts`. I'll write a firebase function with cron job which will extract approved posts from `all_posts` collection and put it in `approved_posts` and then delete that posts from `all_posts`. Then I can simply query from `approved_posts`. will it be a Good Idea? – Sunny May 31 '18 at 08:55
I am still learning and I need to unlearn principal of relational database to get more from NoSql db – Sunny May 31 '18 at 08:57
You can move a document from a location to another, as explained in my answer from this **[post](https://stackoverflow.com/questions/47244403/how-to-move-a-document-in-cloud-firestore)**. So yes, there is an option. You can also create a single collection and add a flag of type boolean named `aproved`. If a post is not aproved give the value of `false`, otherwise the value of `true`, right? Keep me poested. – Alex Mamo May 31 '18 at 09:08
Right I can use boolean for approved but multiple users can approve it, I want to count how many users have approved a post. For base I've choose 5 users. So if minimum 5 users approve it. It'll be count as approved. I'll similarly add disapprove also. – Sunny May 31 '18 at 09:16
That nice. In this case, just add al users as a map within the post, in which every key is the uid of the user and the value is `true`. So you can go ahead with that. – Alex Mamo May 31 '18 at 09:24
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172176/discussion-between-john-smith-and-alex-mamo). – Sunny May 31 '18 at 16:27

How to workaround firestore basic query in my scenario

1 Answers1