Need Help with a Join on a Table and API

bigshooTer · May 1, 2023, 6:32pm

I'm trying to do a classic left join of a table which I created via CSV upload against:

another table created via csv upload (mapping table) and 2) a query that I created, Query JSON w SQL, to put the results of an API into a table on the canvas. API is current prices from coingecko using the markets endpoint.

message:"syntax error at or near "$1""

Problem is I cannot figure out the syntax for the life of me. It's something with the 2nd LEFT JOIN on the CurrentPrices query (or API)... I am currently trying to join on the query which formats the API, but not sure if I should instead be .

1.) I've tried changing the Resource type from default "Retool Database" to "Query JSON w SQL", wondering if changing that was required to join on the query which puts the API into a table. I am not sure if I am supposed to join on the query that puts the API into a table or the API query itself. If the latter, I assumed I would need to formatdataasarray, which I've tried and couldn't get to work either.
2.) For some reason the editor wants Portfolio in parenthesis. After importing csv to create table, I added a column and set the default value. I am joining on this field. Maybe it's not the correct way, but I got it work in Access and haven't messed with it.
3.) Tried to use return.data in the Transformer. I'm not really sure how or when to use this...
4.) I am new to javascript. I know my around with jet sql, m, and vba.

Table 1 - Transactions
Table 2 - Trx Type mapping (used to map trx_type on table 1)
Table 3 - Current Prices from CoinGecko (Query JSON w/ SQL query in table on canvas)

What is wrong with this? Any help would be appreciated.

SELECT
t_trxtypemapping.trx_type,t_transactions.timestamp,t_transactions.description,t_transactions.asset as token,t_transactions.quantity,t_transactions.quantityt_transactions.spot_price as histvalue,'USD' as FeeCurr,t_transactions.fees,(t_transactions.quantityt_transactions.spot_price) + t_transactions.fees as cost, t_transactions."Portfolio"
FROM
(t_transactions
LEFT JOIN t_trxtypemapping ON (t_transactions."Portfolio" = t_trxTypeMapping.portfolio) AND (t_transactions.trx_type = t_trxTypeMapping.trx_type_source))
LEFT JOIN {{qCurrPrices.data)}} as qCurrPrices ON qCurrPrices.Symbol =t_transactions.asset
WHERE ({{!trxtypeFilter.value}} OR t_trxtypemapping.trx_type iLIKE {{'%' + trxtypeFilter.value + '%'}})
ORDER BY token, timestamp DESC

bigshooTer · May 2, 2023, 6:03pm

bump anyone? I'm still stuck on the syntax issue.

Kabirdas · May 2, 2023, 6:20pm

Hey @bigshooTer!

It looks like it would be helpful to use both a RetoolDB query and a Query JSON with SQL query here. Since you're using left joins, can you try splitting this up into two queries?

Fetches and joins data from your Retool Database using it as a resource
Joins the data from the first query with {{ qCurrPrices.data }} using a Query JSON with SQL query

bigshooTer · May 2, 2023, 7:00pm

I simply want to pull a field from the API into an existing select query. I want to an existing table on a canvas which is populated by a query used for formatting purposes and down the road consolidating multiple sources of data in varying formats. It's a giant transaction register.

I have:
1 - table created via csv upload transaction detail
2 - resource query which is https rest api using parameterized url
3 - query json using sql to put #2 into a table format to display on canvas

current query uses #1 for most fields. Attempting to pull in 1 field from #2 or #3. I've been trying to reference the field in the query and the resource api directly.

what's odd (bc this is the second time this has happened to me), is that the issue with syntax all the sudden works today and the issue has shifted to {{!trxtypefilter.value}}. I had issues with this last week. Then it started working the next day randomly....

what's wrong with where clause now. this exact logic works in another query just fine... why does syntax validation seem to change daily?

FROM
(cb LEFT JOIN map ON (cb."Portfolio" = map.portfolio) AND (cb.trx_type = map.trx_type_source))
LEFT JOIN {formatDataAsArray(apiCurrPrices.data)} as qCP ON qCP.Symbol=cb.asset
WHERE ({{!trxtypeFilter.value}} OR map.trx_type iLIKE {{'%' + trxtypeFilter.value + '%'}})
ORDER BY timestamp DESC

Kabirdas · May 3, 2023, 8:02pm

Which resource type are you using to combine the data?

Retool uses AlaSQL under the hood for Query JSON with SQL queries which has a somewhat different syntax than Postgres which is used for Retool Database queries. You can read more about it in these docs!

bigshooTer · May 3, 2023, 11:48pm

Resource = Retool Database

The original query was just a select query of a csv upload with a search bar.

I duplicated that query and am trying to join against an API (or a query which puts that into a table) to add a column from the returned data set.

I did try switching to Query JSON with SQL and that didn't help unfortunately. I've also tried turning off prepared sql.

Goal is to left join on 2 fields in order to pull 1 field into my query.

Kabirdas · May 5, 2023, 10:19pm

What was the code you were using in the Query JSON with SQL query?

bigshooTer · May 10, 2023, 2:19am

the above code in my original post. I had thought since I was joining to a resource which was JSON based, maybe I needed to

bigshooTer · May 10, 2023, 2:43am

I explored unionTrx4 in detail.

I thought I was already doing what you were suggesting. I have a RetoolDB Queries which simply formats RetoolDB data and unionTrx2 was Query JSON with SQL which is the source of the appended field. I changed unionTrx2 back to a RetoolDB query bc I couldn't get it to work Query JSON with SQL. I've even tried joining directly on the rest api.

Anyway, it looks like you:
1 - Set resource to Query JSON as SQL
2 - formatdataasarray on qCoinbase

Why do you need to format the From source as an array if it wasnt JSON to begin with? I was thought you only need to format JSON data sources as array. That looks to be my problem.

bigshooTer · May 10, 2023, 2:53am

Also, why do you need to create a "Select *" ResourceDB query for [map]? Why can't you just select * (or whatever field you choose) directly in the Query JSON with SQL query?

Kabirdas · May 10, 2023, 8:00pm

Retool Database is its own Postgres database existing on a server that you're querying. In contrast, Query JSON with SQL queries only exist in the frontend, they let you do operations on your data using SQL code but don't actually talk to any databases.

The different data format helpers don't actually convert data to JSON, they just convert data between two different structures. Because of how AlaSQL works, it expects data to be formatted as an array of objects, and incidentally, SQL queries will return data as an object of arrays, which is why it needs to be reformatted (as mentioned here).

bigshooTer · May 17, 2023, 2:00am

Got it. That makes sense now. Seems like converting the join to an array of object is what I was missing. Would you mind explaining when the Transformer should and should not be used? I just don't under the purpose of it...

bigshooTer · May 20, 2023, 7:49pm

sorry, I'm still a little confused as to why you created query21. The way I've been seeing it is that querying the resource db directly would be the equivalent of querying a table in Access.

So in order to use the TrxType retool db in a Query JSOn with SQL query, you need to create a query first to format it as an array? Thing is, I dont see mention of format data as array in query21.

Kabirdas · May 22, 2023, 8:25pm

The main purpose of the query is to fetch the data from the Retool Database server since Query JSON with SQL queries can't interface directly with that server. It can serve a secondary purpose of converting the data to an array type but you can also do that in line when you pass the data to the Query JSON with SQL query.

bigshooTer · May 22, 2023, 9:48pm

I went to add 2 simple columns then removed them and saved. Now one of the keys (field comes from the map) shows as "unidentified". I tried copy and pasting the original syntax back in but I get the same error.

unionTrx4 in the community support copy worked fine after you guys added the formatdataasarray on the join & added the staging query for the map in between the retool db and query. I copied the syntax and put it in my version. It worked.

Same code, 2 different results.

My query

My query with code repasted again but having error

Community Support query

Kabirdas · May 25, 2023, 11:07pm

It looks like there might be something that's happened with query20 can you try running it again or examining it to see what it's returning?

This is what I'm seeing normally:

However, if there's something that would cause that transformer to return null or so (for instance, if it errors) I see:

bigshooTer · May 31, 2023, 3:16am

Are you looking in the community support copy? or my copy? query20 was created by you guys. I was referencing the retool db directly before you guys created this and said to reference the query instead. FYI - I just renamed query20 to qMap in my copy.

I don't see anything wrong with query20/qMap. One thing I noticed is that unionTrx4 runs successfully until you actually add a field to the select statement from the resource you are joining against. For example, there is a join on current prices table. I added cPrice to the select statement and I get "undefined" now.

I have to say, working through these syntax issues has been painful. I'm new to js and retool... but am I wrong for thinking a simple join shouldn't be this complicated?

bigshooTer · May 31, 2023, 3:23am

I apologize but I'm still confused on when to use the transformer and what should or should not go in there. I am a MS Access guy so the concept of a transformer is new to me. I honestly just leave it disabled and ignore it.

You've been very informative on this long running thread. I appreciate all your continued help and patience. I think I can really build something here if I can work through a handful of foundational syntax items.

bigshooTer · June 1, 2023, 7:00pm

Hi @Kabirdas, I wanted to follow up on this. I have a few queries with similar syntax and they are all now doing this. Everything stopped working. I'm not sure what happened.

Kabirdas · June 2, 2023, 10:33pm

Hey @bigshooTer,

Sorry for the late reply!

I believe I was looking in the community support copy. Transformers can certainly be tricky to get used to! Here's a somewhat lengthier summary in case it's helpful for some perspective:

One way to think of it is that, whenever you're writing a resource query, you can access anything that's in the "State" tab of your debug console using {{}}:

Additionally, in you can use some JS to make an expression if the value you're referencing isn't the right format or so, e.g. {{ tempState.value + 1 }} instead of just {{ tempState.value }} and there are some helper functions you can use there too like {{ formatDataAsArray(query1.data) }} instead of just {{ query1.data }}.

Beyond that, you can use the query to access whatever resource it's set up to connect to. So, for example, if you have an external Postgres DB you can set up a connection to it and then write queries to that resource to retrieve data. so, if you have two separate Postgres DBs that require separate setups, you have to write two separate queries in order to retrieve data from each one.

RetoolDB and Query JSON with SQL queries are a little special in that:

For RetoolDB queries you are querying an external database but the connection has already been set up for you (if you're on Cloud)
For Query JSON with SQL queries, there's actually no external database you can talk to directly from it

Query JSON with SQL queries are super useful because they have their own SQL syntax (AlaSQL) that makes it easier to combine data from different resources.

So, if you have two Postgres DBs, for example, you'd write your two queries to get data from them, and then write a third Query JSON with SQL query to combine everything. But, it doesn't just have to be SQL resource! In your case, you have a RetoolDB resource (which is a special Postgres DB) and the API resource you're fetching additional data from. You'll still want a total of three queries though, one for RetoolDB, one for your API, and one Query JSON with SQL to combine them.

However, the expressions you're using inside of {{}} could throw an error for a number of different reasons. For instance, if query1 hasn't been triggered yet then formatDataAsArray(query1.data) will fail. This could also be the case if query1 has run but doesn't have the format that formatDataAsArray is expecting! (Wrong format for a formatting helper? ) So it's usually good to check every part of the chain to see that it's returning what you'd expect but that can take some getting used to.