How to limit collectionGroup query results to an ancestor?

Question

I've been reading the blog post https://firebase.googleblog.com/2019/06/understanding-collection-group-queries.html to better understand the collectionGroup queries.

Although, I still have one question: how can I limit the results to a specific ancestor. Let me explain myself.

Imagine I have companies that manufacture cars that have tyres. We have different brands of tyres, used in different cars. In the end, we have a many-to-many relationship. I know I should not use this term in the NoSQL world, but I call a dog a dog :-)

Anyway, my question is the following: If we have a shortage in a company A of a specific tyre brand (let's say Michelin), you would need to flag this tyre as out of stock. I would think to run a collectionGroup query such as:

db.collectionQuery("tyre")
  .where("brand", "==", "Michelin")
  .get()
  .then(function (querySnapshot) {
    // update flag accordingly
  })

But that would update the stock of other companies.

My question is: how would you narrow the collectionGroup query results so you only update the tyres info from company A?

I could include the company A docRef in the tyres collection and use where() to narrow the results. It seems like a valid approach. Although, it would be a mix between a top-level collection and a subcollection. Is it best practice?

UPDATE

Actually, I'm following the example of the restaurants to put my hands on firebase/firestore. A restaurant can have multiple menus. A menu can have multiple items. Items can be reused and therefore present in multiple menus.

collection('restaurants').doc(..).collection('menus').doc(..).collection('items')

I like to think that's the best way to structure the data (vs. a top-level collection for the items). But items like Coffee can easily be found in multiple menus of multiple restaurants. If one restaurant is short on coffee, how can I update the coffee items for that specific restaurant using something like:

db.collectionQuery("items")
  .where("name", "==", "Coffee")
  .get()
  .then(function (querySnapshot) {
    // set available = false
  })

Can you give more details on your database structure (exact list of collections and subcollections and their parent/child relationships) and the context in which you want to execute the query? — Renaud Tarnec, Jun 21 '20 at 10:38
Hi @RenaudTarnec I've updated the question to avoid the comment size limitation — Binajmen, Jun 21 '20 at 11:37
"how can I limit the results to a specific ancestor?" You can't. Collection group queries currently read across all collections with the given name. There is no way to restrict them to a specific path. The typical workaround is to name the collections differently to allow the use-case. Also see https://stackoverflow.com/a/58977074, https://stackoverflow.com/a/61034013, https://stackoverflow.com/a/56224106 — Frank van Puffelen, Jun 21 '20 at 15:06

Renaud Tarnec · Accepted Answer · 2020-06-23T07:14:58.867

If one restaurant is short on coffee, how can I update the coffee items for that specific restaurant?

By using a collectionGroup query you could do like that:

  db.collectionQuery('items')
    .where('name', '==', 'Coffee')
    .get()
    .then(function (querySnapshot) {
      querySnapshot.forEach(function (doc) {
        const itemQuantity = doc.data().itemQuantity;
        if (itemQuantity === 0) {
          const restaurantRef = doc.ref.parent.parent.parent.parent;
          return restaurantRef.update( {....})
        }
      });
    });

by alternatively using the parent properties of DocumentReference and CollectionReference.

However, this may not be the most efficient and affordable way if you have a lot of restaurants, because your collectionGroup query will return a lot of records.

A more efficient way would be to keep a set of counters and watch them, through either Firestore listeners or Cloud Functions.

Finally, note an important point: you write "A menu can have multiple items. Items can be reused and therefore present in multiple menus". Note that items documents in

collection('restaurants').doc('r1').collection('menus').doc('m1').collection('items')

and in

collection('restaurants').doc('r1').collection('menus').doc('m2').collection('items')

are totally different documents. This is different from the SQL world where different records from one table can point to the same record of another table.

Conclusion: You should most probably have one itemsStock collection per restaurant, and each time one of the items is "consumed/ordered" you decrease its count by using FieldValue.increment(-1).

In other words, I advise to separate the collections of items that compose a menu from the one which holds the items counters (i.e. the itemsStock collection). The first ones are dedicated to menus items selection and the second one dedicated to managing the stock of the restaurant. When a guest/customer chooses/orders an item you only decrease the collection holding the items counters.

Update following your comment:

If you want to update all the "lasagna" items in all the menus of a restaurant (for example to add an ingredient, as you mentioned in your comment), a very common approach is indeed to modify all the corresponding docs (this is called data duplication in the NoSQL world).

You would use the exact code at the top of my answer: you query all the "lasagna" items documents in all the menus of the restaurant and update them. You could trigger this process by a Cloud Function that would "watch" a master collection in which you have reference items: each time you change a doc of this collection (i.e. an item) you update all the similar/corresponding items doc in the menus subcollections.

I agree with your final note, and I understand these are two different collections. But conceptually, from the owner POV, a lasagna in the weekend and weekday menu is the same lasagna. I would duplicate the lasagna in NoSQL, but an action on one lasagna (put `instock: false` flag) should be replicate on all lasagna. Hence my original question. I do believe keeping the subcollection is the best structure approach. I would need the restaurant docRef in every items. That way, if I can update all lasagnas info via a collectionGroup but for a specific restaurant (if what I say makes sense :-)) — Binajmen, Jun 22 '20 at 05:41
"an action on one lasagna (put instock: false flag) should be replicate on all lasagna" -> This is why I advised to separate the collections of items that compose a menu and the one which holds the items counters. The first ones are dedicated to menus items selection and the second one to manage the stock of the restaurant. When a guest/customer chooses/orders an item you only decrease the collection holding the items counters. — Renaud Tarnec, Jun 22 '20 at 11:09
This approach seems to apply quite well for that specific case. However, if I add nuts to the recipe, I would need to update the allergy info for all the items lasagna. Would you keep such info in a separacte collection as well? — Binajmen, Jun 22 '20 at 13:50

score 0 · Answer 2 · answered Jun 21 '20 at 16:21

I could include the company A docRef in the tyres collection and use where() to narrow the results. It seems like a valid approach. Although, it would be a mix between a top-level collection and a subcollection. Is it best practice?

This is a common approach, since the only way to filter documents in a collection group query is using the fields of the documents. You can't use anything in the path of the document as a filter. It's common to duplicate data in NoSQL type databases in order to facilitate queries.

However, you probably don't want to have a top-level collection with the same name as child collections, if you want to limit the queries to just the child collections.

Indeed, I only want subcollection `items` holding the ref of their ancestor `restaurants`. That way I can perform a collectionGroup on `items` with a where condition on the `restaurant` ref. I felt wrong to repeat such a "conceptual relationship" info as the `items` are nested in the `restaurant` and therefore have an intrinsic relationship already. I guess I was hoping for something like `db.forAncestor(restaurantRef).collectionGroup("items")` :-D But the reminder "It's common to duplicate data in NoSQL" works for me as well :) — Binajmen, Jun 22 '20 at 05:54

How to limit collectionGroup query results to an ancestor?

2 Answers2

Update following your comment: