Flutter Firestore Database Ordering Most Liked Documents in the Last 24 Hours-CodePudding

I only want to show 10 documents that are in descending order by numberOfLikes and are created in the last 24 hours. But whenever I use where(), and orderBy(), the logic fails. After running the code below, I added the index Firebase required, but the documents are ordered by most recent published instead of most liked. Is this not possible this way, if so how else can I achieve this? Note: numberOfLikes is a number in firestore database. I would appreciate any help. Thank you.

StreamBuilder(
              stream: FirebaseFirestore.instance
                  .collection('posts')
                  .where("datePublished",
                      isGreaterThan:
                          DateTime.now().subtract(const Duration(hours: 24)))
                  .orderBy("datePublished", descending: true)
                  .orderBy("numberOfLikes", descending: true)
                  .limit(10)
                  .snapshots(),
              builder: (context,
                  AsyncSnapshot<QuerySnapshot<Map<String, dynamic>>> snapshot) {
                if (snapshot.connectionState == ConnectionState.waiting) {
                  return Center(child: CircularProgressIndicator());
                }
                if (snapshot.hasError) {
                  print(snapshot.error);
                  return Text("No Posts Found");
                }
                return Container(
                  height: MediaQuery.of(context).size.height * .37,
                  width: MediaQuery.of(context).size.width * .98,
                  child: ListView.builder(
                    scrollDirection: Axis.horizontal,
                    itemCount: snapshot.data!.docs.length,
                    itemBuilder: (context, index) => TrendingPostsCard(
                      snap: snapshot.data!.docs[index].data(),
                    ),
                  ),
                );
              },
            ),

CodePudding user response：

Since you always need to order on the field you want to perform a relational condition (i.e. >=) on, you can't get the top X upvoted posts within a certain range.

You can:

Either get all posts for the date range and then order/filter the top 10 in your application code.
Or get the top N posts across all dates, and then filter by date range. The problem here is that N is not a known value.

This limitation is inherent to how Firestore (and many NoSQL databases) works, as they use a single index for the entire query - and only one > type operation is possible. For more on this, also see Todd's excellent video on Getting to know Cloud Firestore, specifically How do queries work in Cloud Firestore? You may also learn about such intricacies from my own talk about implementing geoqueries on Firebase and Firestore.

If you want to be able to perform both condition in the query, you'll need to ensure you have an equality condition for one of the parts. For example, if you keep a map with the date for each post, you can perform an equality query to get the posts of today and then use the orderBy/limit combination for the most liked posts.