Conditional Select

[leclep]

I am trying to run a conditional query based on the value of a switch. This is what I tried but I get a syntax error problem at or near if. Can someone please help. Thanks

if {{switch1.value}} ==true then
select * from schema.table1
else
select * from schema.table2

[minijohn]

Hey @leclep,

If you Disable converting queries to prepared statements for your resource you can do this:

SELECT
  *
FROM
  {{ switch1.value == true ? 'schema.table1' : 'schema.table2' }}
  

[leclep]

Hey @minijohn. Thanks for your reply but it doesn't quite address the question because I wasn't clear enough. I need the app to choose between two distinct select statements based on the value of the switch so it's not just a simple choice of table. One select statement is on a table and the other is on a join. That's why I need something like, if switch value = false select_statement else select statement. Don't even know if that's possible in sql.

[minijohn]

Got ya,

then I would abstract the logic that chooses which data you need in a JS query and have two individual queries you can choose from.

[leclep]

I'm very grateful for your help. I'm not a developer so i didn't even know that was possible. I did Google it but there weren't any examples I could find. Could you maybe share witih me some code so I can get an idea of the syntax. I have the sql ready to insert in the if condition then else statement if you could just show me how I'd be eternally graateful.

[minijohn]

No worries :))

Check out this and this.

But to achieve this you would need to write some code

I'm assuming you're having a table that reads from that data and you want to choose which query you trigger based on the switch right?

[leclep]

Thanks - I checked out both the links but couldn't find and example of a js script which runs sql queries based on a condition.

[minijohn]

Try this:

var response

if ({{toggle1.value}}) {
  response = {{get_user_attributes.data}}
} else {
  response = {{get_users.data}}
}

return response

You would use transformer2.value for your table.

Does that work for you?

[leclep]

I'll give that a go later on but thanks for helping. Off topic but do you happen to know what happened to the dropdown component? The documentation refers to it but all I can find is the selec control and that doesn't take an initial value. It's driving me mental

[minijohn]

Sure thing, let me know if it worked.

The Dropdown component is called Select now. Check out the component reference here.

You can set the default value (initial) of the select component here:

If you need to programmatically set the value you can use:

select2.setValue('bar')

[leclep]

select * from admin.organization where {{!toggle1.value}} OR id in (select org_id from admin.agreements) order by ID

[leclep]

This was the solution. Retool support came up with that.

[leclep]

Now I have another issue though so if it isn't breaking the forum rules here's the issue. Updating from a multi-select selection. This is the code I have but it's only updating the first bundle_id and not the others.

update admin.node 
set bundle_id = {{tableBundles.selectedRow.data.id}}
WHERE admin.node.id = {{parseInt(multiselectNodesAvailable.value)}}

[Chris-Thompson]

Hey @Ieclep!

Happy to help here! Are you looking to update multiple records selected in your multiselectNotesAvailable component? If so, would using something like:

WHERE admin.node.id = ANY({{multiselectNodesAvailable.value}})

work here provided {{multiselectNodesAvailable.value}} returns an array of integers?

[leclep]

I ended up using the GUI and id in {{}} which did the trick. Tried what you suggested in sql couldn't get it to work. For some reason it wasn't recognizing it as an array even with mapping. Solved now though. Thanks