Using Dart/Flutter and the package firebase_storage: ^11.7.7, I am building the method getQuestions that receives the following parameters below. I need to search for questions in the database using these filters that can be null, considering that they are lists coming from multiple selects and firestore does not accept multiple where in, what is the best solution for this problem, since the limit in a first search can harm the filtering if done locally and if the limit parameter is removed the search can be very costly because there are millions of questions records in the database?

String? creatorUserId;
String? type;
bool? isAnnulled;
String? statement;
List<String>? examCompanyIds;
List<String>? institutionIds;
List<String>? disciplineIds;
List<String>? topicIds;
List<String>? positionIds;
List<String>? educationFieldIds;
List<String>? regionIds;
List<String>? areaIds;
List<String>? scholarityIds;
List<String>? difficultyIds;
List<String>? yearIds;
DateTime? createdAtStart;
DateTime? createdAtEnd;
int limit;

I’ve tried what’s below and it didn’t work. I’m not a NoSQL expert, I would actually like to know from you what the best solution is.

Note: The questions collection data is denormalized.

Future<List<QuestionModel>> getQuestions({
    required GetQuestionsParams params,
  }) async {
    try {
      // Authorize the user with Firebase Authentication.
      await DataSourceUtils.authorizeUser(_auth);

      final querySnapshots = <QuerySnapshot>[];

      // Base query
      Query query = _firestore.collection('questions');

      // Adds common filters
      if (params.creatorUserId != null) {
        query = query.where('creatorUserId', isEqualTo: params.creatorUserId);
      }
      if (params.type != null) {
        query = query.where('type', isEqualTo: params.type);
      }
      if (params.isAnnulled != null) {
        query = query.where('isAnnulled', isEqualTo: params.isAnnulled);
      }
      if (params.statement != null && params.statement!.isNotEmpty) {
        query = query
            .where('statementText', isGreaterThanOrEqualTo: params.statement)
            .where(
              'statementText',
              isLessThanOrEqualTo: '${params.statement}uf8ff',
            );
      }
      if (params.createdAtStart != null) {
        query = query.where(
          'createdAt',
          isGreaterThanOrEqualTo: Timestamp.fromDate(params.createdAtStart!),
        );
      }
      if (params.createdAtEnd != null) {
        query = query.where(
          'createdAt',
          isLessThanOrEqualTo: Timestamp.fromDate(params.createdAtEnd!),
        );
      }

      // Helper function to apply `whereIn` to the first parameter with list
      // greater than 1
      var appliedWhereIn = false;
      void applyWhereIn(List<String>? ids, String fieldName) {
        if (!appliedWhereIn && ids != null && ids.length > 1) {
          query = query.where(fieldName, whereIn: ids);
          appliedWhereIn = true;
        }
      }

      // Apply `whereIn` to relevant parameters
      applyWhereIn(params.yearIds, 'year.uid');
      applyWhereIn(params.positionIds, 'position.uid');
      applyWhereIn(params.institutionIds, 'institution.uid');
      applyWhereIn(params.examCompanyIds, 'examCompany.uid');
      applyWhereIn(params.topicIds, 'topic.uid');
      applyWhereIn(params.disciplineIds, 'discipline.uid');
      applyWhereIn(params.educationFieldIds, 'educationField.uid');
      applyWhereIn(params.areaIds, 'area.uid');
      applyWhereIn(params.regionIds, 'region.uid');
      applyWhereIn(params.scholarityIds, 'scholarity.uid');
      applyWhereIn(params.difficultyIds, 'difficulty.uid');

      // Executes the base query
      querySnapshots.add(await query.get());

      // Filter results with `whereIn` manually
      List<DocumentSnapshot> filteredDocs =
          querySnapshots.expand((qs) => qs.docs).toList();

      // Helper function to apply filter manually
      void applyManualFilter(List<String>? ids, String fieldName) {
        if (ids != null && ids.length > 1 && !appliedWhereIn) {
          filteredDocs = filteredDocs.where((doc) {
            return ids.contains((doc[fieldName] as DataMap)['uid']);
          }).toList();
        } else if (ids != null && ids.length == 1) {
          query = query.where('$fieldName.uid', isEqualTo: ids.first);
        }
      }

      // Apply manual or `where` filters depending on the case
      applyManualFilter(params.yearIds, 'year');
      applyManualFilter(params.positionIds, 'position');
      applyManualFilter(params.institutionIds, 'institution');
      applyManualFilter(params.examCompanyIds, 'examCompany');
      applyManualFilter(params.topicIds, 'topic');
      applyManualFilter(params.disciplineIds, 'discipline');
      applyManualFilter(params.educationFieldIds, 'educationField');
      applyManualFilter(params.regionIds, 'region');
      applyManualFilter(params.areaIds, 'area');
      applyManualFilter(params.scholarityIds, 'scholarity');
      applyManualFilter(params.difficultyIds, 'difficulty');

      // Applies the final limit to manually filtered documents
      filteredDocs = filteredDocs.take(params.limit).toList();

      // Converts filtered documents into QuestionModel
      return filteredDocs
          .map((doc) => QuestionModel.fromMap(doc.data()! as DataMap))
          .toList();
    } catch (exception) {
      final serverException = ExceptionHandler.handleException(
        exception,
        'QuestionRemoteDataSrcImp | getQuestions',
      );

      throw serverException;
    }
  }