Firebase query with multiple combined WHERE conditions ie where (condition1) OR where(condition2)

Question

I wish to combined multiple where conditions when querying some firebase collections. Below is what works, but want to extend it further.

const db = admin.firestore();

const snapshot = await db.collection(collectionId).where("published", "!=", false).get();

The above works but would like to add another OR where("another_property", "!=", "some_value") to achieve what I need, but can't get the syntax right.

Answer 1

In NoSQL databases, you should structure your data around the queries you are going to make rather than your twist your queries around your data.

Let's go old school for a second and look at the truth table of your query:

A	B	A'	B'	A' OR B'
0	0	1	1	1
1	0	0	1	1
0	1	1	0	1
1	1	0	0	0

So, of the data you are querying against, if both published and another_value match the values you are negating, exclude them from the result.

This leads to the simplest approach available - concatenate the values of both in a new field - and then add it as a negation in your query.

"/somedoc": {
  "published": false,
  "another_property": "some_value",
  "published_another_property": "false_some_value"
}

Then to query for the results you are looking for:

const snapshot = await db.collection(collectionId)
  .where("published_another_property", "!=", [false, "some_value"].join("_"))
  .get();

Using the modular SDK, you could create your own QueryConstraint builder:

const whereNotAll = (fieldMap) => {
  const keys = Object.keys(fieldMap).sort(); // note: keys are now in lexicographic order
                                             // so for the above fields, it'd need "another_property_published" instead
  const values = keys.map(key => fieldMap[key]);
  return where(keys.join("_"), "!=", values.join("_"))
}

const constraint = whereNot({ "published": false, "another_property": "some_value" });
// equivalent of where("another_property_published", "!=", "some_value_false");