Database Table to Array, for Nested List

[jonj]

I'm trying with a simple table to understand the basic concepts of how to do this, before moving to more complex data.

In my table I have 7 rows and 3 columns of interest. I ultimately want to group the data in lists by Ticket

The closest I have got is by using this SQL query

SELECT
JSON_AGG(
JSON_BUILD_OBJECT('TicketNo',"Ticket",'Task', "Task", 'Desc', Description)
) AS "Ticket_Details"
FROM
"Ticket_Tasks"
GROUP BY
"Ticket"
ORDER BY "Ticket"

Which gives this result

I'm not sure if that is the right format, and if it is am struggling to build up the path with the correct ri i and numbers, I get this far and run out of ideas.

{{TableToArray.data.Ticket_Details[ri[0]].}}

Would appreciate some pointers, either on the formatting of the JavaScript, or if there is an easier/better way to get from db table to a useable array.

Thanks in advance,
Jon.

[jonj]

Using @Kabirdas' great help here, I have this now working. I'm not sure if it's the most efficient (yet) as it's three SQL queries, but it works very nicely.

1. Query Table (queryTable)

SELECT * FROM Table

2. Convert Data to Array (tableToArray)

SELECT * FROM {{formatDataAsArray(queryTable.data)}}

3. Convert Array to Array of objects (ticketArrayObjects)

SELECT Ticket ,array(@{Task: Task, Description: Description}) as Tasks
FROM {{tableToArray.data}} GROUP BY Ticket

Query result looks like this

Ticket (the group by) value can be accessed at

{{ticketArrayObjects.data[ri[0]].Ticket}}

The individual values (Task, and Description) can be accessed at

{{ transformedTicketstoArray.data[ri[0]].Tasks[ri[1]].Task }}
{{ transformedTicketstoArray.data[ri[0]].Tasks[ri[1]].Description }}

Gives something like this (not formatted yet)

[27shutterclicks]

I did something almost identical in Retool Mobile recently and it was very easy, since in Mobile you can assign data sets to a list view, which makes looping through the records of a data set a breeze.

Conversely, in Web Apps, you have to work off the main query and keep track of ri's and i's which can get confusing.

In your scenario, although seemingly simple, there is a fundamental issue with the data structure, which adds unnecessary complexity to everything.

Typically, in an actual application, you would likely not have the data structured that way in the database. Instead, tickets would be in their own table with their own property columns (ticket id, ticket status, ticket category, etc.)

Then, in a separate table, you'd have a list of tasks, associated with a ticket id. It is here where your scenario starts. The difference is you're missing a step, and that is the initial select of the unique ticket id, through which you would loop and request the associated tasks from the tasks table.

So first you'd have something like: "Select * from ticketsTable where ticketStatus = active", and then for every row/ticket id from that query, you'd loop to get the tasks: "Select * from tasksTable where ticket_id = ... "

The first order of things is to get your data structured as above. For this, in Retool, you don't need multiple queries like you mentioned. You need only a basic one, and then some further processing to transform the query results in the right format (using a query transformer).

The main (and only) query is:

select * from ticketsTable;

Then, on the same query, enable a transformer:

//determine the number of unique tickets (using underscore library function)
const ticketsArray = _.uniq(data.ticket); //e.g. output: [123,125,127]

//initialize an empty array
let ticketsObj = [];

//loop through the array of unique tickets
for (const ticket of ticketsArray){
  
  //get the ticket tasks by filtering the sql query output
  let ticketTasks = formatDataAsArray(data).filter(tasks => tasks.ticket == ticket);
  
  //add ticket number and its tasks to tickets object
  ticketsObj.push(
    {
      ticket_id: ticket,
      tasks: ticketTasks
    }
  )
}

return ticketsObj;

The above transforms the query output in this format:

With the data in this format (based off a single getTickets query), the rest follows nicely:

There is also some useful information in this docs article: https://docs.retool.com/docs/create-custom-list-views

[jonj]

Thank you Andrei (@27shutterclicks),

I really appreciate all the time and effort you put in to explain everything so clearly. Having the transformation code and screenshots helped so much.

I've got this working now from your valuable explanation.

I totally agree about the two table approach, one for tickets, another for tasks, which I already have. How would I approach doing as you suggest;

• Find all active tickets
• For each active ticket loop through to find all active tasks.
• transform the data as before.

Thanks again,
Jon.

[27shutterclicks]

To find active tickets, you'd need to add a column to the tickets table with the ticket status. Similarly, to find active tasks, you need a (task) status column in the tasks table.

Tickets table:

Tasks table:

Then, you query the two tables using a join select :

SELECT * 
FROM tickets_db
  JOIN tasks_db 
    ON tickets_db.id = tasks_db.ticket_id
WHERE tickets_db.status = 'open'
  AND tasks_db.status = 'active'
ORDER BY tickets_db.id;

Which gives this result:

And that's it. Since all the logic and data structuring happens in the transformer, the list and all the nested components will update automatically.

If you want to make it as sexy as in the screenshot and add filtering dropdowns, just update the select query to accept dynamic parameters for ticket and task status.

I actually didn't know how to do this and I had to look it up. The challenge was not to pass the parameters, but how to go about resetting them - how to display all tickets unfiltered if nothing is selected for ticket or task status (if the dropdown value is null). The solution was using the COALESCE function.

SELECT * 
FROM tickets_db
  JOIN tasks_db 
    ON tickets_db.id = tasks_db.ticket_id
WHERE COALESCE(tickets_db.status = {{selTicketStatus.value}},TRUE)
  AND COALESCE(tasks_db.status = {{selTaskStatus.value}},TRUE)
ORDER BY tickets_db.id;

Add some bells and whistles like status-based ticket and status colors and call it a day.

[jonj]

Thanks again Andrei @27shutterclicks),

Really appreciate the time and effort you spent explaining and documenting that. The sexy extra touches of the colour coding and the dropdowns is really nice, and very nicely demonstrates the flexibility and possibilities with Retool.

I was confused when you mentioned looping through results, and was thinking that was in the SQL (not in the transformer), but a join makes perfect sense. Single query, then use a transformer. Love the simplicity.

An alternate way to do dynamically filter on the dropdowns, which you solved with

WHERE COALESCE(tickets_db.status = {{selTicketStatus.value}},TRUE)
AND COALESCE(tasks_db.status = {{selTaskStatus.value}},TRUE)

is this

WHERE ( {{!selTicketStatus.value }} OR tickets_db.status = {{selTicketStatus.value}} )
AND ( {{!selTaskStatus.value}} OR tasks_db.status = {{selTaskStatus.value}} )

[27shutterclicks]

Thank you for the tip on the WHERE clause, noted.

For looping, I was initially thinking you could just get a list of tickets based on status and then for each ticket lookup the corresponding tickets - basically two simpler selects.

In some regards, this would make some things simpler (like having a list of unique ticket IDs from the start, that you could easily use to populate the top level list with ticket numbers), but it would add much more complexity afterwards as far as dynamic filtering goes based on status from two tables.

The join select makes filtering easy, but then forces you to add logic to determine how many unique tickets are there (since the join basically gives you a list of unique tasks).

If you did have separate selects for tickets and tasks, you could loop through each ticket using dynamic index (i) variables in the tasks query.

Something like:

SELECT * from tasks_db WHERE ticket_id = {{getTickets.data[i].id}}

But then again, I think you'd quickly hit other roadblocks.

The use of the transformer keep things simple, because it gives you the most control of the data structure that is exposed to all the components that may use it. If you don't have control over the structure at the query/transformer level, and you implement the necessary logic at component level, then whenever you need to make changes, instead of making them in the transformer, you'd need to go through each component and change things. It would get mind-numbing really fast. For me at least :).

Cheers.