Searching within collection collectionGroup query

Question

So I came across this answer which allows limiting the collectionGroup query to a specific document: CollectionGroupQuery but limit search to subcollections under a particular document

However I also want to further filter results based on a specific field using where, which required an index. The query works without errors but it always returns empty snapshot:

const cityRef = firebase.firestore().doc('cities/cityId');

firebase.firestore().collectionGroup('attractions')
  .where('name', '>=', keywords),
  .where('name', '<=', keywords + '\uf8ff')
  .orderBy('name')
  .orderBy(firebase.firestore.FieldPath.documentId())
  .startAt(cityRef.path),
  .endAt(cityRef.path + "\uf8ff")
  .get()
  .then((querySnapshot) => {
    console.log("Found " + querySnapshot.size + " docs");
    querySnapshot.forEach((doc) => console.log("> " + doc.ref.path))
  })
  .catch((err) => {
    console.error("Failed to execute query", err);
  })

Can you *start* the query with the condition on the field path, and thus also order on that field first? — Frank van Puffelen, Mar 08 '22 at 04:30
@FrankvanPuffelen Sorry how do you mean? Move `startAt` and `endAt` before `where`? Tried that and still 0 docs. — eozzy, Mar 08 '22 at 04:57

Frank van Puffelen · Accepted Answer · 2022-03-10T21:34:07.733

firebaser here

The problem is almost certainly that your query has range checks on two different fields (name and the document path), which isn't possible in Firestore's query model. As the documentation on query limitations says:

In a compound query, range (<, <=, >, >=) and not equals (!=, not-in) comparisons must all filter on the same field.

Your startAt and endAt clauses are just different ways of writing > and < as far as this limitation is concerned.

To understand why the SDK allow you to write this query, but doesn't give you the result you want, we'll have to dive a bit deeper into it, so...

What is possible, is to pass all relevant fields to startAt and endAt so that it can determine the correct slice across all those field values.

Doing that would also remove the need to even have the where, so it'd be:

firebase.firestore().collectionGroup('attractions')
  .orderBy('name')
  .orderBy(firebase.firestore.FieldPath.documentId())
  .startAt(keywords, cityRef.path),
  .endAt(keywords + '\uf8ff', cityRef.path + "\uf8ff")
  .get()
  ...

But this query now first looks for the documents starting at keywords and then if necessary for cityRef.path in there to disambiguate between multiple results.

What you want is the equivalent of this query:

const docId = firebase.firestore.FieldPath.documentId()l
firebase.firestore().collectionGroup('attractions')
  .orderBy('name')
  .where('name', '>=', keywords),
  .where('name', '<=', keywords + '\uf8ff')
  .orderBy(firebase.firestore.FieldPath.documentId())
  .where(docId, '>=', cityRef.path),
  .where(docId, '<=', cityRef.path + '\uf8ff')

Now it's immediately clear why this isn't possible, because we have range conditions on two fields.

I've been trying to get that working in this jsbin (https://jsbin.com/yiyifin/edit?js,console), so far without success, but I'll post back if I get it working or have a final verdict on why it doesn't work.

Ah, bummer! So there's no way to restrict collectionGroup query to a specific collection/document and also range checks at the same time? — eozzy, Mar 08 '22 at 07:34
Hey @eozzy: after much more back and forth and testing, it indeed seems my original conclusion on why this doesn't work is correct. I edited my answer to clarify why it isn't possible in Firestore's query model at the moment. — Frank van Puffelen, Mar 10 '22 at 21:35
Ah thanks for that. I ended up saving the keywords in an array and using array-contains-any working reasonably ok for now. — eozzy, Mar 11 '22 at 21:56

Searching within collection collectionGroup query

1 Answers1