Best practice for firebase firestore queries (can't do joins so what do I do)

I'm looking for guidance from any firebase/firestore users.

In particular I'm looking for best practice patterns when trying to get data from reference collections.

since I can't do a traditional join on a firebase/firestore db, I need a way to replace the normal sql queries when using firebase/firestore.
I have created workarounds, but nothing scalable.
job collection contains a reference field to the company collection.
Results from job collection may look like this

{"name" : "Million dollar job", "company": {  "$ref": "companies/pOjUBNOX9lHAsVkA2OEQ"} }

but I want this

{"name" : "Million dollar job", "company": "IBM" }

I have created a workaround by getting the entire companies collection and filtering it with javascript...

but this workaround is not scalable

(because companies collection will become thousands of rows)
here is the workaround code in a custom column

{{{return entry._id ===$ref.slice(-20)})[0].name }}

I was thinking that I could call an external function while iterating to getCompanyById, but I cant do trigger from a custom field (perhaps there is a sneaky way to do that?), I also can't call trigger from a transformer.

Does anyone have any solutions they have come up with?

Hi jnui!

Thanks for the question - working with related objects in Firestore can be cumbersome due to their non-relational architecture.

A couple of questions for you as well:

  • What is the end goal you're aiming for? Do you need to preload all companies names into the UI or could you do a lookup with each selected document for instance? If that is the case, you could query Firestore for each company name when a row is selected.
  • Is it possible to run a cloud function that adds/updates a value to your job collection with the explicit name for the company? This could run periodically and give you the value directly without the need to run multiple queries on the frontend.

These videos might provide a little more helpful context as well for modifying the relationships/values in Firebase (if that is an option).

I hope this helps and looking forward to better understanding your goals - thanks!