CodeOverload
CodeOverload

Reputation: 48565

Query by array item and other fields at once in Firestore

I had to rephrase this question since it was a bit misleading (my fault).

Here is my dilemma, let's say I have a party collection:

parties {
  status: "open",
  invitees: [56486,68978,897650], # user ids of invited users
  scheduled_at: 1948089050 # timestamp
}

I'd like to query only "open" parties, that I'm invited to (my user id in the invitees array), and sorted by scheduled_at

I could solve the first part of querying the array by turning it into a hash (thanks to @renaud and @james poag):

parties {
  status: "open",
  invitees: {
    56486: true,
    68978: true,
    897650: true
  }
  scheduled_at: 1948089050
}

Now performing this:

db.collection('parties').where('status', '==', 'open').where('invitees.56486', '==', true').orderBy('scheduled_at')

Results in a firebase error asking me to make a composite index for status + invitees.56486 + scheduled_at. as you can see it's impractical for me to add an index for each user id.

Any ideas?

Upvotes: 0

Views: 313

Answers (1)

Doug Stevenson
Doug Stevenson

Reputation: 317968

It looks like you're trying to make a query against a schema that doesn't really support that query. You're going to have to adjust your schema (possibly duplicating data between collections) to support your intended query. This sort of practice is normal for NoSQL type databases.

You're going to need a new collection that relates a single party with a single invitee, one for each combination, that effectively serves as a "join" between them:

party-invitees
  - party_id
  - party_status ("open")
  - party_scheduled_at
  - attendee_id

Now you can find out which open parties an attendee is invited to:

db.collection('party-invitees')
    .where('party_status', '==', 'open')
    .where('attendee_id', '==', 'whatever')
    .orderBy('party_scheduled_at')

Bear in mind that you'll have to change this collection along with any other collections with the same data as they change. Fortunately, batch writes and transactions make this easier to do atomically.

Upvotes: 1

Related Questions