Transform results of SQL query

[Anand]

Hi,

I am trying to loop over the SQL query results using a transformer and manipulate some data before it is added to the table component.

The SQL query would return the following results:

In the result set above if the deleted_at field is null the State column of the table should be "Cancelled" and if the cancel_at field is null, the State column of the table should be "Deleted".

Here's what the expected end result should look like:

I have been stuck with this for the past 3 days and haven't been able to figure out a solution. Can someone please help?

Thanks,
Anand

[msd5079]

does something like this work..?

formatDataAsArray(data).map(result => {
  if (result.deleted_at === null && result.cancelled_at !== null) {
    result.state = 'Cancelled';
  } else if (result.deleted_at !== null && result.cancelled_at === null) {
    result.state = 'Deleted';
  }
  return result
});

[Anand]

Hi Matt,

You code works spot on, thank you!

However, it doesn't update the "State" column in the table. I think I now see what the issue is. State field is a custom column added to the table component, when I added a console.log statement in your code and I can see all the fields of the database except State. So that could be the issue. See this screenshot:

I think the issue now boils down to, how do we update the value of a custom column added to the table component.

Thank you so much for your help, appreciate it!

Regards,
Anand

[msd5079]

are you running that in the actual transformer section of your query? If so I dont think you need to add the let data = {{ table1.data }} just reference data directly.

But anyways, depending on your data source...i find the easiest way sometimes is to just add a 'fake column' in the query...something like

select col1,col2,col3, NULL as State from myDb - that is in a MySQL database, fyi. But somethign similar should be possible in other db's, and then you can simply work with that column like the others...edit the value int he transformer etc, and not have to mess with custom columns.

-MD

[msd5079]

Now that you say its a custom column actually - you could just leave the original query alone entirely, and just use some ternary inside the custom column like so:

{{ 
currentRow.deleted_at === null && currentRow.cancelled_at !== null
    ? 'Cancelled'
    : currentRow.deleted_at !== null && currentRow.cancelled_at === null
      ? 'Deleted'
      : '' 
}}

This way no need to modify the original, etc.

Thanks!

-MD

[Anand]

Hi Matt,

After your first reply and having figured out that the custom column was the issue, adding a fake column to the query was the first thing I tried.

However, the updating the column in the loop didn't work, not sure if we need to refresh the table data. Here's what I tired:

select col1, col2, null as status from myDB

Then I used this loop, which didn't update the table, not sure if we need to refresh the table

let data = {{table1.data}};

formatDataAsArray(data).map(result => {
  if (result.deleted_at === null && result.cancel_at !== null) {
    result.status = 'Cancelled';
  } else if (result.deleted_at !== null && result.cancel_at === null) {
    result.status = 'Deleted';
  } else {
    result.status = 'Active';
  }
  return result;
});

The ternary operator works like a charm, so I will mark that as the solution, thanks a ton!

But I'd still like to know and learn how to update a table column and get the data to appear in the component UI.

Regards,
Anand

[msd5079]

Glad you have it working!

Few things:

you dont really need to 'update your table' after modifying, if you use the transformer built into the query function like seen here:

this way, just execute your query - modify the query result - then have the data directly linked.

If you WANT to use an external javscript query to update a table - its a bit more painful, atleast in my opinion.

you have to pull the table data, modify it, then use table.setData() and repopulate the entire thing.

like so:

Let me know if you need anything else!

-MD

[Anand]

Hi Matt,

I completely agree with you, using the transformer built into the query works far more better then external one.

Using an external JS transformer is definitely a pain, I keep getting table1 is not defined error in the external transformer. Here's the screenshot:

Once again, I sincerely thank you for your help and time.

Have a good weekend!

Regards,
Anand

[msd5079]

Well in that particular SS, you have unnecessary {{}} around your let data = table1.data definition - remove that and it should work.

{{}} are only needed outside of actual JS queries to tell retool to interpret what you are writing as JS.

[Kabirdas]

@Anand just want to pop in here to mention that from the screenshot it looks like you're using a transformer. It's important to use a JavaScript query here like @msd5079 is referring to, otherwise, you won't be able to call setData on your table.