1

I'd like to perform a Firestore query to return an ordered list of blogs where the blog has an array that contains all elements of a query array.

An example is blog data such as this:

{
  "cities": ["Tokyo", "Kyoto", "Osaka", "Nara"],
  "modified": 1645457445
}

{
  "cities": ["Prague", "Bratislava", "Budapest"],
  "modified": 1645450245
}

{
  "cities": ["Hiroshima", "Kyoto", "Tokyo"],
  "modified": 1645453845
}

I want to get a page of 20 blogs that contains both "Tokyo" and "Kyoto", ordered by the most recently modified (descending).

I have seen the following questions
Firestore query - array contains all
How to perform compound queries with logical AND on array in Cloud Firestore?

When applying the above suggestions so the blog data looks like this:

{
  "cities": {
    "Hiroshima": true,
    "Kyoto": true,
    "Tokyo": true
  },
  "modified": 1645453845
}

And using the query

    var db = admin.firestore();
    var query = db.collection("blogs")

    data.cities.forEach(tag => {
          query = query.where("cities." + tag, "==", true)
    })

    query = query.orderBy("modified", "desc");

    if (typeof data.modified !== "undefined") {
      query = query.startAfter(data.modified)
    }

    return query.limit(20).get()

Firestore errors and requests to create an index such as:

cities.Tokyo Ascending modified Descending

The suggested index is not suitable as the blogs can contain any city in the world so it isn't feasible to create an index for every city

How can I achieve this query in firestore?

thirstycoda
  • 11
  • 2
  • Can you share your query? You'll need to create some indexes when ordering. If you just use `where()` to match cities and then order after fetching results, it might be easier. – Dharmaraj Feb 21 '22 at 15:56
  • @Dharmaraj, I've edited the question to include the mention of paging and added an example of my query. If I don't use order as part of the query, I can't use limit as I would need the full set for accurate ordering. This could result in a greater number of reads and an increased cost – thirstycoda Feb 21 '22 at 16:13
  • Please check the duplicate to see how you can create an index. – Alex Mamo Feb 21 '22 at 17:53
  • @AlexMamo sorry but I don't understand how this question is a duplicate of the one you have suggested. The question you have linked this to does not have an array of values that the document must match. Also, the answer of just create a index is not feasible here when the values in the array can be any city in the world. I've edited the question to clarify that the index suggested by Firestore is not suitable. Please reopen this question – thirstycoda Feb 21 '22 at 18:10
  • As long as you get that warning, an index is required, otherwise, it won't work. Besides that, please check the second duplicate where Frank provided a solution that solves such problems. Since you cannot create an index for each city, "you should consider augmenting your data structure to allow a reverse lookup.". – Alex Mamo Feb 21 '22 at 18:34
  • @AlexMamo ok, so to confirm, firestore doesn't support array-contains-all, I can't use the workaround of using a map instead of an array as suggested in the questions my question description references because that doesn't work with ordering. The suggestion is to create a cities collection and every time a blog is created that references a city, the blog id is added to the city's document in the cities collection. How do I combine this with ordering by blog modified date time? – thirstycoda Feb 21 '22 at 18:49
  • I reopened the question, so maybe other will help. – Alex Mamo Feb 21 '22 at 18:51

1 Answers1

0

one viable solution is to create an index array that contains the elements together

{
  index: ['a', 'b', 'c', 'a b', 'a c', 'b c', 'a b c']
  or 
  index: ['a', 'b', 'c', 'ab', 'ac', 'bc', 'abc']
}

this document can be found by:

  • one of the categories
  • some of the categories
  • all of the categories

you have to sort the elements inside every index alphabetically before insertion and the elements inside the query

dsl400
  • 322
  • 3
  • 14