I am working on an app that helps users search for their desired medication in nearby pharmacies then shows a list of the pharmacies that have the drug in stock with prices.

Here is the structure of the database:

Firestore-root
  |
  --- pharmacies (collection)
        |
        --- $pharmacyId (document)
               |
               --- medicine (sub-collection)
                     |
                     --- $medicineId
                           |
                           --- name: "Aspirin"
                           |
                           --- inStock: true
                           |
                           --- price: 500

I used to CollectionGroup query on FireFoo to get the pharmacies that have "Aspirin" in stock, for example, I got the promote for index creation and created the required index but the query result is empty.

Here is the query:

async function run() {
  const query = await db.collectionGroup("medicine")
    .where("name", "==", "Aspirin")
    .where("inStock", "==", "true")
    .get();
  return query;
}

Here are some images for clarification:

Pharmacies collection with 2 Pharmacy documents

medicine subcollection inside the 1st pharmacy document

medicine subcollection inside the 2nd pharmacy document

CollectionGroup index

Query Result

CodePudding user response：

The issue in the following query:

async function run() {
  const query = await db.collectionGroup("medicine")
    .where("name", "==", "Aspirin")
    .where("inStock", "==", "true")
    .get();
  return query;
}

Lies in the second .where() call. In the database, your inStock field holds a boolean value while you are searching for documents that hold a string value, and this is not correct. To solve this, simply use:

async function run() {
  const query = await db.collectionGroup("medicine")
    .where("name", "==", "Aspirin")
    .where("inStock", "==", true) //No double quotes
    .get();
  return query;
}