How to I join data from two Firestore collections into a single table?

[sampl]

Hi, I want to show data from two Firestore collections in a single Retool table.

Specifically, I have a "Users" Retool table connected to a Firestore Users collection. I also have a Subscriptions collection with the userId in each document, and I want to show subscription information on each user in Retool.

This post suggests a SQL join, but Firebase doesn't have joins unfortunately (AFAIK).

It also mentions transformers, but the tranformer docs say that I can't trigger a query in transformers.

Can Retool handle this case? Really hoping so, it looks like an awesome tool and I'd love to use it instead of DIY'ing an admin panel!

[ben]

Hi @sampl! Welcome to the forum

Yes, this is certainly possible in Retool. One option is pulling in the values needed from both collections, and then using a query within Retool to join them. Here's a general outline of this:

Have a getUsers query which runs on page load, and on success triggers our getOrgs query:

Then our getOrgs query which triggers on success our join query:

Then finally our join query, which uses SQL syntax to join the json objects:

Then we can set our table to be the result of this join query:

[sampl]

Oh man… this is working perfectly. Exactly what I hoped for. I didn’t know about the Query JSON with SQL.

@ben thank you so much! Retool is awesome.