Lists in parameterized database queries

1

We’ve added support for typed lists as inputs to database queries. This is a long-requested and convenient feature. For example, here is how we can transform a query to northwind taking a single string parameter for a country:

--input: string country
select * from customers where country = @country

into a query taking a comma-separated list of countries:

--input: list<string> countries
select * from customers where country = ANY(@countries)

After running this query, a dialog will appear with a field for entering a comma-separated list of values:

image
image512×597 25.7 KB

Essentially, this is a substitute of a parameter @name into an ARRAY. You can wrap this array in other types of the range comparison operator, such as <= ANY or > ANY, which is especially useful for numeric types, and, in general, in anything which argument is an ARRAY.

Currently only string type is supported for lists. We will add support for all types along with specifying lists providers in the query forms, so that it becomes possible to source lists from currently available dataframes’ columns or results of running other queries.

  • Learn more about using the lists feature in this video: link.
  • Learn more about parameterized queries in Datagrok following this video: link, and Help.
1 Like
2

Hi @dskatov,

Is it possible to use a table column as a query like the list?
Because I would like to get data from our own DB with ID which is in a table.
What I would like to do is…

–name: _test
–friendlyName: Browser
–connection: DBNAME
–input: dataframe data [Input data table]
–input: column ID
set echo on
SELECT TABLE.C1
,TABLE.C2
FROM TABLE
WHERE TABLE.ID IN (@ID)

Thanks,
Taka

3

Hi Taka,
Right now you can pass DataFrames as a parameters only in PostgresDart provider

–name: _test
–friendlyName: Browser
–connection: DBNAME
–input: dataframe data [Input data table]
set echo on
SELECT TABLE.C1
,TABLE.C2
FROM TABLE INNER JOIN #data ON ...

None of other providers support DataFrame parameters yet, but it’s on our roadmap to implement it.

You can write JS function, that accepts DataFrame and column and returns list and specify it as an editor in Data Query

--input: list<string> ids {editor: Admin:ColumnConverter}
select * from table where id = ANY(@ids)

JS script:

//name: ColumnConverter
//language: javascript
//input: dataframe table
//input: column col
//output: list<string> lst

let categories = col.categories;
let data = col.getRawData();
lst = Array.from({length: data.length}, (_, i) => {return categories[data[i]];})

Please, let me know if you have any questions

2 Likes
4

Hi @alex.paramonov ,
Thanks for your advice. It’s like a magic! I could get data with column as a query.
And I have additional question.
After running the SQL I would like to add(Join) the results to input dataframe. Is it possible to do it?
If yes, could you please teach me how to do it?
Thanks for your kind support.
Best,
Taka

1 Like
5

It’s a tricky part.
We have multiple ways to achieve that,
first, Join Table function. You can join result table to your table using key column.
second, there is an {action: join} directive to output data, but you need to maintain ids order column length and in this particular case it’s unusable because of editor abstraction.

So, I suggest to try join tables, and I’m gonna think how to implement this use-case (getting additional columns from DataQuery by id) in the core.

2 Likes
6

Hi @alex.paramonov,
Thanks for your suggestion {action: join} worked well in my case but it seems dangerous as you said. So I will to use table join after running the sql.
But it will be step by step process. I would like to make 1 step workflow in the feature.
If you have any useful idea to do it. Please let me know.

1 Like
7

Hi Taka,
We’ve decided to improve action.join in 1.19, so it will be able to join by ID.

Until then, here is an example for you how to write a JS script that executes a DataQuery and then joins additional data.

//name: JoinNewIds
//language: javascript
//input: dataframe table
//input: column col

let newDf = await grok.functions.call('Admin:Test', {ids: col.toList()});
table.join(newDf, [col.name], ['id'], null, null, DG.JOIN_TYPE.LEFT, true);

and the Admin:Test DataQuery:

--input: list<string> ids
select id, id || ' ' || id as new_id from (select unnest(@ids) id) t

The query needs to return old ID with a bunch of new columns and then JS function joins them in-place.

2 Likes