Solving firestore whereIn query limitation

When using firestore, you can only pass an array of 10 items in a compound query, which is used as whereIn. I want to go over to solve this problem.

The Problem

I’ll briefly review the problem for those who still don’t know what I mean.

Find posts from your followers!

It would be best if you did this in SQL like the one below.

SELECT *
FROM posts
WHERE user_name IN ('Jerry', 'Tom', 'Alice', 'Messi', 'Ronaldo' ...);

However, the problem in firestore is that it limits the value length to 10, so you can only pass ten followers to the query. Therefore, you will not be able to get all followers’ posts.

Current status (2023 January)

This is a critical issue for developers since these are very often used. Of course, there are lots of questions related to this, which are listed below.

Stackoverflow questions

• https://stackoverflow.com/questions/64603392/get-more-than-10-documents-from-firestore-in-one-round-trip-using-flutter
• https://stackoverflow.com/questions/59257753/firebase-firestore-query-an-array-of-more-than-10-elements
• https://stackoverflow.com/questions/61354866/is-there-a-workaround-for-the-firebase-query-in-limit-to-10
• https://stackoverflow.com/questions/70851638/firestore-how-to-handle-more-than-10-in-equalities

If you check the links, you can see the only way to achieve this is to merge the results. I worked on this a few years ago with typescript, but today I am working on Flutter, so I planned to redo this in Dartlang.

Approach

Merge multiple lists.

/// 1. Cut whereIn list by 10.
var whereInList1 = followers.sublist(
  0,
  followers.length > 10 ? 10 : followers.length,
);

/// 2. Fetch first list of followers' posts.
var firstPosts =
    await PostRepository.instance.getMany(
  whereIn: whereInList1,
  size: size,
);

/// 3. Cut whereIn list by 10.
var whereInList2 = followers.length > 10
    ? followers.sublist(
        10,
        followers.length > 20 ? 20 : followers.length,
      )
    : [];

/// 4. Fetch second list of followers' posts.
var secondPosts = followers.length > 10
    ? await PostRepository.instance.getMany(
        whereIn: whereInList2,
        size: size,
      )
    : [];


var fetchedPosts = <PostModel>[
  ...firstPosts,
  ...secondPosts,
];

/// 5. Get the last cursor from merged list
fetchedPosts.sort((a, b) => b.createdAt.compareTo(a.createdAt));

return fetchedPosts;

For those of you who want to know what the getMany function does, check out here.

First, you might think of merging two lists like the above and repeating yourself until you achieve all the data. However, this is very tedious and not interesting 😕.

Before going further, if you are willing to paginate the merged query, you should get the latest cursor from the merged lists. Therefore, we should sort them after merging.

When I put all my thoughts together, the requirements are listed below.

1. Cut whereIn lists in 10 and fetch queries separately. Unlike the above queries, we need to fetch more than 20, so we need to merge multiple lists.
2. Merge lists and order them to get the latest cursor.

Solution

1. Define firestore query.

Future<List<Map<String, dynamic>>> _queryList({
  /// The reference to collection
  required CollectionReference<Map<String, dynamic>> reference,
  required List<Object> whereInList,
  startAfter,
  int size = 10,
}) async {
  var query = reference
      .limit(size)
      .where('userRef', whereIn: whereInList);

  query = query.orderBy('createdAt', descending: true);

  query = startAfter != null ? query.startAfter([startAfter]) : query;
  var snap = await query.get();

  if (snap.docs.isEmpty) {
    return [];
  }

  return snap.docs.map((e) => e.data()).toList();
}

2. Implement common queries for fetching multiple [whereIn].

Future<List<T>> getWhereInQueryList<T>({
    required List<Object> whereInList,
    int size = 10,
    startAfter,
  }) async {
  var whereInListBy10 = [];

  for (var i = 0; i < ((whereInListBy10.length / 10) + 1); i++) {
    var current10 = i * 10;
    var next10 = (i + 1) * 10;

    var whereIn = whereInList.length > current10
        ? whereInList.sublist(
            current10,
            whereInList.length > next10 ? next10 : whereInList.length,
          )
        : <Object>[];

    whereInListBy10.add(whereIn);
  }

  var fetchedList = <T>[];

  for (var ref in whereInListBy10) {
    var i = whereInListBy10.indexOf(ref);
    var whereInList = whereInListBy10[i];

    var fetchedItems = await _queryList(
      whereInList: whereInList,
      size: size,
      startAfter: startAfter,
    );

    fetchedList.addAll(fetchedItems as List<T>);
  }

  return fetchedList;
}

The code written above is developed to meet the requirements. I split whereInList by 10 and fetched them separately. In the end, I merged them all.

3. Time to use it.

var list = await getWhereInQueryList(
  reference: FirebaseFirestore.instance
    .collection('your_app')
    .doc('app')
    .collection('posts'),
  whereInList: followerRefs,
  size: size,
  startAfter: startAfter,
);

var posts = <PostModel>[];

for (var el in list) {
  var post = PostModel.fromJson(el);
  posts.add(post);
}

posts.sort((a, b) => b.createdAt.compareTo(a.createdAt));

return posts;

We finally earned a common firestore query, which can query lists with infinite whereIn list.

Hope this helps. Full implementation is at my gist.

Thanks for reading 👍