Getting Data from *Single* Database Result

Feature Requests
,
1

There are many cases where I use a query I like to call "find_resource", which simply fetches a single row from the database. I use this to fill up my form values.

However, these values are currently wrapped inside of an array (i.e. treated as bulk), meaning that I cannot access the inner values very easily.

Although I am currently using the following JavaScript function to retrieve the actual values, this feels a bit hacky:

var newData = {};

// get the first value from the value array set
_.forEach(data, (value, field) => {
  newData[field] = _.first(value);
});

return newData;

Although this is a simple fix, I could imagine another field in the Settings tab, allowing for "get values from single result". While this has the benefit of accessibility, it also allows for more strict checking if you are in fact getting a single result (otherwise it could show an error).

I could imagine this setting being implicit due to the use of a LIMIT 1 clause, but I guess that would not be as clear and could lead to unforeseen circumstances.

Let me know what y'all think! :slight_smile: :wave:

2

Hey @emozio,

Although this seems also a hack with its own risks, wouldn't it be easier to refer to find_resource.data[0] ?

I may be completely missing the point though...

3

My apologies, I simplified my explanation when I referred to "wrapped within an array". In reality, there are arrays for each column. This probably has to do with Retool's internal logic/implementation model.

When you're dealing with grids and tables alike, this is not a problem. However, once you want to format the data as a single object (i.e. columns and values), then the current structure of Retool is not what you want.

P.s. You can try it out for yourself, by calling console,log(data) in the query transformer panel.

4

Ah yes. I'm so used to transform all of my queries with return formatDataAsArray(data) using the query embedded transformer that for a second I forgot the regular output of a query comes by columns

5

Yes, that would indeed solve the issue partially. I guess my point is that these kind of transformations are pretty common and should deserve an option, perhaps in the Advanced tab.

6

Hello folks!

Yes I agree, the DB connectors do a lot of data transforming to set up query results to be very easy for the table component to read.

As trying to do the reverse of what you are describing would be a lot trickier :sweat_smile:

I fully empathize with @MiguelOrtiz as I tend to use

const singleObjArray = formatDataAsArray(data)
return singleObjArray[0]

In transformers far more often that I would like to disclose.

I can definitely make a feature request to add into a query's Advanced settings a toggle that would effectively add in a transformer that would apply the above code block as a built in transformer!

1 Like
7

Yes, I suppose if we are all using this technique, then it should probably be added as a default. Not sure about UX, but I could imagine an option that can be checked on or off whether to use as single row/result.

Another, more pragmatic approach would be to offer a couple of standardized transformers, like $getFirstResult out of the box, but it would be hard letting the user know this is available.

In any case, I believe this aspect of the query builder is too common of a requirement to ignore.

1 Like