How to work with multiselect in SQL queries

How do I do X?
1

Hello,
I have a multiselect dropdown list. For each selected value I need to create a row in a table:

I’m trying to write SQL request like:

INSERT INTO channels(channel_name, customer_id) VALUES
({{multiSelect.selectedOptionValues}}, {{table1.selectedRow.id}});

But obviously, it’s creating a single row with an array as a value, however I would like that each selected value in my multiSelect widget generates a new row.

1 Like
2

Hi @mikko

To insert more than one row, the VALUES(...) have to be formatted with each row wrapped in parantheses and separated by commas:

INSERT INTO channels(channel_name, customer_id) VALUES

(
  ('channel_name', 'customer_id'), 
  ('channel_name', 'customer_id'), 
  ('channel_name', 'customer_id'), 
  ('channel_name', 'customer_id')

  );

To dynamically generate this format from the selected rows of a table,

map() over the selectedRows with a function that returns the desired values with the correct formatting applied to each line.

This function should work for most cases but I suspect it will fail with leading/trailing quotes in a field value.

{{  (() => {
const rows = table1.selectedRows;

const values = rows.map(row => [ 
// create 2D Array of values [[],[],[]], then map again to add formatting

multiSelect.selectedOptionValues, row.customer_id

].map(i => '"'+i+'"')
);
		
return values.reduce((a, v, i) => {
	return a.concat('(' + v + ')')
},[]).join()   })()
}}

NOTE: In the Query Settings, Use Prepared Statements must be turned off to pass values using this method.

I prefer to create a separate text widget to store the ‘values’ string, and then bind it to the Insert statement.

INSERT INTO channels(channel_name, customer_id) VALUES

(

  {{text1.text}}

  );

Then put the above code in text1’s default value.

3

Here’s an example from another app with different table/field names, but it’s the same approach:

image

4

Thanks @GreenFlux
I tried to use map function, but I didn’t disable Use Prepared Statements.
It didn’t work without it

Nice idea about temporal text field

1 Like