Burn Firebase to the ground

This post summarizes a frustrating limitation in Firebase that limits inequality filters, and how I ended up working around it.

Goal

• Being able to add a Comment to a Post.
• Retrieve a list of most commented posts, created this week.

Problem

If you include a filter with a range comparison (<, <=, >, >=), your first ordering must be on the same field

Which means that when I naively run my query below, I end up with an error saying

Inequality filter property and first sort order must be the same: createdAt and numComments

Naive unsupported query

// This won't work
const query = firestore
	.collection("posts")
	.orderBy("numComments", "desc")
	.where("createdAt", ">", "2021-09-01");

Context

Firebase: data modelling

Our Posts is a (root-level) Collection in Firebase, with Comments being a SubCollection under each post.

Google: Firebase data structures

Firebase: calculating number of comments

Where we in Postgres could count number of references from a Comment table, there is no api for finding number of Comments for a Post.

The way to solve it (without having to read the entire subcollection), is to add a field on Post and increment it when adding a new Comment.

const postRef = firestore.collection("posts").doc(postId);

const commentRef = postRef.collection("comments").doc("new-random-id");

const comment = {
	// comment to add
};

await firestore.runTransaction(async (transaction) => {
	transaction.update(postRef, {
		numComments: FieldValue.increment(1),
	});
	transaction.set(commentRef, data);
	return data;
});

Stackoverflow: Cloud Firestore collection count

Firebase: Adding date subfields

Now comes the frustration: I can’t order by numComments, while also filtering by date greater (or less) than.

To work around this, I’m adding new date-related fields to the Post: createdAtYear, createdAtMonth and createdAtWeek.

I can then approximate my previous query with

const query = firestore
	.collection("posts")
	.orderBy("numComments", "desc")
	.where("createdAtYear", "==", 2021)
	.where("createdAtWeek", "==", 39);

Obviously, this is no where near as flexible as having a greater-than filter. If I’d want to serve last 12 months for instance, I’d have to query for last year + this year, and filter out the too-old posts client side.

Sidenote: Firebase pagination

Pagination is done with

const query = firestore
	.collection("posts")
	.orderBy("numComments", "desc")
	// ....
	.startAfter(cursor);

Note that the cursor have to match the value of the first orderBy clause. In the example above, cursor must be a number.

If you have multiple orderBy, you can specify them all there, in the same order.

const query = firestore
	.collection("posts")
	.orderBy("numComments", "desc")
	.orderBy("createdAt", "desc")
	// ....
	.startAfter(lastResultNumComments, lastResultCreatedAt);

For this reason, it’s a good idea to have the last result when doing pagination (rather than just the value of a certain field).

Stackoverflow: Cloud Firestore collection count