Linking a table using firebase/firestore

Queries and Resources
, ,
1

I've created a new topic rather than continuing the conversation in another thread.

  • I have a table with my users (userTable) displaying my firebase Auth query (getUsers)
    Table uses this for data:
{{ selectCollection.value != "users" ? getDocuments.data : getUsers.data.users }}
  • I have another table (DocTable) that shows me all the documents associated with that selectedRow user.
    Table uses this data:
{{ showDocuments.data }}

Show documents is a query with the following:
firestore > query firestore >

users/{{ userTable.selectedRow.data.uid }}/blocks

As such I can see the total number of documents the user has in a summary text block that I have which has the following:

Displaying documents belonging to:
Email: {{ userTable.selectedRow.data.email }}
UID: {{ userTable.selectedRow.data.uid }}
Total file count less rootblock: {{ DocTable.displayedData.length-1 }}

This shows me how many documents the currently selected user has.

What I'm trying to do:

  • I want to see the number of documents that each user has in a column in the userTable so I can sort and filter by document numbers (e.g. see who has tonnes, get averages etc etc)

With lot's of help in a previous thread, I've been told to investigate JSON SQL queries so I'm looking into that and learning about them but haven't managed to mesh my understanding with retool.

What I've done so far:

  • Created a resource 'Query JSON with SQL' (tableLink) and put in the following:
Select userTable.*, dt.docLen
from userTable as ut
join DocTable as dt on ut.uid=dt.uid

docLen is a transformer I made which is feeding my text box summary above.

I've had a go playing with changing:

tablename.columnname values
removing docLen to see if I can get anything up into the column.
inputting the original queries instead of the table name.

So far I'm either seeing:

message:"Table does not exists: userTable"

Or I'm seeing lot's and lot's of data.

In the custom column of my userTable I am putting {{ tableLink }} or variants on that.

I've been reading on SQL select, from, join etc but there are a few additional layers of abstraction because of retool+firebase which are confusing me at the examples I'm looking at.

Addendum: One of the issues present with what I have is that I'm joining userTable and DocTable to try get the number of documents each user has but the Doctable only shows the selectedUser. That's what I suspect anyway but I'm not sure what to do about it. Is a table join still appropriate?

2

Hey @arkonis!

Looking at your Query JSON with SQL query,

Select userTable.*, dt.docLen from userTable as ut join DocTable as dt on ut.uid=dt.uid

userTable and DocTable aren't defined anywhere, so you'll need to do something like:

Select userTable.*, dt.docLen from {{getUsers.data}} as ut join {{getDocuments.data}} as dt on ut.uid=dt.uid

If your query data is currently an object of arrays instead of an array of objects, you'll need to do something like:

Select userTable.*, dt.docLen from {{formatDataAsArray(getUsers.data)}} as ut join {{formatDataAsArray(getDocuments.data)}} as dt on ut.uid=dt.uid

https://docs.retool.com/docs/querying-via-sql#joining-two-json-arrays

Is this what you're looking to do? :slight_smile: Let me know!