How to merge 2 queries into a table and make one of them editable

App Building
1

I have a table hosted on my app Postgres. The table called Messages and it contain customers messages to support. The columns of the table are: rowId, MessageId, UserId, Message
I've created a query for that called getAllMessages

I've created a reTool DataBase with a table called flaggedMessages. It contain the customer messages that the team flagged to handle. The columns of the table are: rowId, MessageId, Reason, Comment
I've created a query for that called getAllFlaggedMessages

The key for these two tables is MessageId
Not all customer's messages will be flagged.

  1. I want to create a reTool app that displays in a table all customer's messages. For every message that was flagged, to display also Reason and the Comment.
  2. I also want to allow our CS to "flag" the customers messages by commenting in the table (so the Reason and Comment should be editable). I will have a button to save all comments.

I'm struggle with topic 1 (for no good reason). Can someone help me with that?
Much appreciated.

2

Hi, welcome to the forums

That's a really good write up and description of the situation, it really helps narrow down possible solutions.
You can combine the data from your two data sources in a couple of ways:

  1. You could write a transformer that uses javascript to merge the two data arrays based on the MessageId - essentially a "loop" - and use the output of this transformer as the data source of the table
  2. You could write some SQL using an AlaSQL query, there's an example of the docs page that sounds like just the scenario you're trying to achieve: Query JSON with SQL | Retool Docs
select *
from {{ getAllMessages.data }} as messages
left join {{ getAllFlaggedMessages.data }} as flagged
    on messages.MessageId= flagged.MessageId

Perosnally, option 2 sounds the best for you as you're clearly fine with writing SQL queries.

There are some caveats here around how many records, if there's pagination going on etc which could affect performance and long term you may want to (for example) get the list of flagged messages first then use this as a query parameter on the getAllMessages query.

2 Likes
3

Thank you very much @dcartlidge