How to make where condition with different firestore collection in flutter stream builder?

Question

is there anyway to match a condidion for a field that is in a different collection? I have two collections: DealsCollection where it contains profile id field. would like to take it and much some other fields in a second collection "Profiles Collection" is that possible on firestore side or on app side?

I tried this code but it is not working because of the missing index.

   StreamBuilder<QuerySnapshot>(
                stream: FirebaseFirestore.instance
                    .collection('VIPDealsCollection')
                    .where('Deal_status', isEqualTo: "Active")
                    .where('Deal_type', isEqualTo: "discount")
                    .where('Deal_amount', isNotEqualTo: "")
                    .orderBy("Deal_amount", descending: true)
                    .snapshots(),
                builder: (context, snapshot) {
                  if (!snapshot.hasData) {
                    return const SizedBox();
                  } else {
                    if (snapshot.data!.docs.isNotEmpty) {
                      return StreamBuilder<DocumentSnapshot>(
                          stream: FirebaseFirestore.instance
                              .collection('BusinessProfilesCollection')
                              .doc(snapshot.data!.docs[index]["Deal_profile_id"])// this is index that missing.
                              .snapshots(),
                          builder: (context, secsnapshot) {
                            if (!secsnapshot.hasData) {
                              return const SizedBox();
                            } else {
                              return Container( // here I will be doing some filters to get a filtered list to build my final widget);
                            }
                          });
                    } else {
                      return Container(
                        height: double.infinity,
                        child: Text(
                          "There is no deals yet, we will be adding soon"
                              .tr,
                          style: GoogleFonts.lato(
                              fontStyle: FontStyle.normal,
                              color: Colors.grey[850],
                              fontSize: 11.sp,
                              fontWeight: FontWeight.w700),
                          textAlign: TextAlign.center,
                          maxLines: 1,
                          overflow: TextOverflow.ellipsis,
                          softWrap: false,
                        ),
                      );
                    }
                  }
                })

Answer 1

Firestore queries can only filter data based on values in the documents that they return.

The only way to filter on values from another collections is to replicate those values in the documents that your query returns.

Also see:

• Where condition in firestore subcollection in ReactJS
• How to fetch all documents from a firebase collection where each document has some sub collection and in sub collection there is a document?