Firestore sort by field with a time condition

Question

I use firestore in a flutter application. I have a collection of post. Each post have a number of like, and a creation date. I would like get the most liked posts in the last week, last day, last month... So i have to do a isGreaterThanOrEqualTo on the time and sort by like. But this is impossible, because i can't sort on two field. Actualy to resolve this problem i have add in my post document 3 field : "month" (month number), "week" (week number), "day" (day number).

And my query is like :

Query query = firestore.collection("post");
query = query.where("month", isEqual: 3);
query = query.orderBy("like", descending: true);
QuerySnapshot data = await queryResult.getDocuments();

It works very well. But I would like the most liked posts of the last 30 days, not really a specific month. And I don't know if it's possible to do that with firestore. Get all the documents and sorting afterwards is not a suitable solution for me.

Is there a solution to this problem?

Firestore does not allow having a sort on a field that isn't also used as a range query. So, if you ask for a range of 30 days past some timestamp, you won't be able to sort on another field. If you absolutely must have a query sorted by likes, you're going to have to come up with a way to determine which documents are in the required time range without using an actual range query. — Doug Stevenson
This is what I thought, what I want to do is not possible without a "hack". Thank you in any case for your response — montauvergne

montauvergne montauvergne · Accepted Answer · 2020-05-15T17:20:29

Doug has confirmed the impossibility of the thing. What I intend to do is simple. I will keep my fields day, month and I will add a field "timeRange" (This field will take a value ranging from 1 to 10, to locate approximately the hour of the question in the day). I will use the "IN" queries, which are limited to 10 field.

So if I want the questions of the last 24 hours for example i do that: (In this exemple i suppose we are actualy in the timeRange 5)

A query for the post of the day :

Query query = firestore.collection("post");
query = query.where("day", isEquals: currentDay);
query = query.where("timeRange", inArray: [0,1,2,3,4,5]);
query = query.orderBy("like", descending: true);
QuerySnapshot data = await queryResult.getDocuments();

A query for the post of yesterday:

Query query = firestore.collection("post");
query = query.where("day", isEquals: yesterday);
query = query.where("timeRange", inArray: [10,9,8,7,6]);
query = query.orderBy("like", descending: true);
QuerySnapshot data = await queryResult.getDocuments();

If anyone has a comment on this solution it is welcome.

Firestore sort by field with a time condition

1 Answers