Match columns between separate data sources using a variable

💬 Queries and Resources
1

  • Goal: I have two separate data sources (mysql and retooldb), they have a common field (sku). The mysql datasource has customerId, sku retooldb has customerName, sku. I'm looking for a way to find the customerName from retooldb given customerId, I plan to match with sku. I will likely maintain this relationship mapping with a retool workflow.

  • Steps: I created a retool variable, sku_list, that's populated via an event handler, on query select sku from mysql where customerId = {{table.selectedRow.data.sku}} when I select customerId from a retool table (populated by a query: select distinct(customerId) from mysql). I'm trying to use variable sku_list in my query against retooldb select customerName from retooldb where sku in ({{sku_list.value.sku}})

  • Details: Since retooldb is expecting a list of integers for the where sku in () it's failing because I'm passing an array.

I'm not sure if I'm overcomplicating things, hopefully my goal is clear.

I'm happy to provide further context if requested.

Thanks in advance.

2

Hello @unclespeedo -- welcome back!

To answer the question asked, I think you would need to format the sku_list.value as an Object and then call upon the sku property to have it use the array. You will, however, most likely need to take this array and use your DB's string_split method to actually format the array properly for the query.

This:
select customerName from retooldb where sku in ({{sku_list.value.sku}})
would look more like
select customerName from retooldb where sku in (SELECT * FROM string_split( {{formatDataAsObject(sku_list.value).sku.toString() }}, ',') )
as noted here you will need to use the Postgresql version with unnest

3

Thanks, I think I was able to follow, this brings me closer but now I'm getting error: "operator does not exist: integer = text"

This is what I came up with:

select customerName from retooldb
where sku IN (
  SELECT
    unnest(string_to_array({{(sku_list.value).sku.toString() }}, ','))
  );

I can see that it's expanding the sku_list but I'm getting the error:

error:true
message:"operator does not exist: integer = text"
position:36
isRetoolSystemError:false

image
image1282×248 23.8 KB

4

This is definitely related to a type mismatch between the data. Is it possible to cast or convert the unnest results to be integers instead of strings? Also, is sku always an integer value or could it be represented as a string in your DB?

1 Like
5

great point, I will explore this path.

in the mysqldb it's mixed types so we store them as strings. For this task, we only care about the integers so I'll look for a way to drop/ignore the sku that can't be converted.

In the retooldb it's an INT4.

6

I changed the original mysql query that's populating sku_list to only include integers

SELECT
  sku
FROM
  mysql
WHERE
  customerId = {{ customer_list.selectedRow.data.id }}
  AND sku REGEXP '^[0-9]+$';

Then I changed the postgresql query for retooldb

SELECT customerName 
FROM retooldb
WHERE sku IN (
  SELECT
    CAST(unnest(string_to_array({{(sku_list.value).sku.toString()}}, ',')) AS integer)
);

I'm no longer receiving any errors but no values are being returned.

If I look at the inspector, I can see that the query looks like the output below, but I have no idea if this is what it should look like, I would have expected/hoped to see SELECT customerName FROM retooldb WHERE sku IN (179206,190879,164605,164611,164515,232587,817445,233312,699116,880401,125300,125305,819151,479881,384412,36365,157362,210807,512376,469494,248518,617555,51854,761767,623553,195038,195035,133207)

SELECT customerName FROM retooldb WHERE sku IN ( SELECT CAST(unnest(string_to_array(179206,190879,164605,164611,164515,232587,817445,233312,699116,880401,125300,125305,819151,479881,384412,36365,157362,210807,512376,469494,248518,617555,51854,761767,623553,195038,195035,133207, ',')) AS integer) );

I'm also curious how the comma was added at the end of the list of integers.

7

Sometimes it helps to hardcode the numbers while troubleshooting

If you run this query, does it return any results?

SELECT customerName 
FROM retooldb
WHERE sku IN (179206, 190879,164605,164611)

Also, it's worth double checking that you do not have prepared statements disabled.

8

Thanks @Tess, I'm embarrassed to admit that this led me to the solution. I only had a subset of the data in the retooldb (10k of 90k+ records) for testing.

And, thanks to you as well @pyrrho, your earlier efforts are what ultimately led me to the appropriate queries.

I believe my issues are resolved, thanks to both of you for your time.

2 Likes