What is the best way to dynamically set the a query's datasource based on what was selected in a dropdown, trigger the query and refresh a table that is associated with the query?
The easiest way to do this is to add WHERE to your query where you would reference the dropdown component, and the query would run each time you change the value in the component.
So it would be
SELECT * FROM
WHERE {{component.value}} = coresponding column
Hope this helps!
this will probably come down to personal preference, but I like setting up things the way you have it using the automatic run feature based on inputs. the alternative would be to directly use event handlers, so on the dropdown you'd have a 'change' or 'onChange' event that either runs the query or runs js. the biggest reason I prefer this is probably because all my code is in one place. if i need to delete, modify or add to something I don't have to click around like crazy trying to find the right component that's housing the event i'm looking for. I prefer using a more reactive programming style (think pull/watch) as opposed to event-driven (push), which lets me focus on processing data when I have it as opposed to triggering an event based on certain conditions.
Hi @Milan_Kalem The query needs to be executed in different datasources/databases based on the dropdown. Unfortunately a where clause will not achieve that, but thanks for your input...
You can use event handler and in the advanced section of the event handler you can set up to only run when certain conditions are met.
Here is the example. Not sure how your select component is structured, but you can have multiple event handlers set to run various queries that fetch the data from different datasources/databases based on what item you select in the component dropdown.
Hope this helps
@bobthebear I like that approach. I tried this little javascript but it didn't work.
What javascript could work in this case?
We use a concept called sharding where every database has exactly the same set of tables to distribute database load. Wile this approach works, for 10 companies I would have to create 10 query controls with exactly the same sql which is probably not the most maintainable. Being able to dynamically set the datasource of a query in my mind is the more maintainable approach but I don't know how to do that yet...
query1.trigger({
additionalScope: {
resourceNameOverride: CompanyDropdown.value
}
});
then in query1 you can directly use {{ resourceNameOverride }} without any scope/namespace resolution preceding the variable name.
alternatively in query1 you could use {{ CompanyDropdown.value }} if you want to bind it directly to the component. this might be preferable if you know the value can only come from this source.
@bobthebear do you mean to change the query based on dropdown? In my case I'm trying to keep the query the same across all databases, and just to point the same query to a different database. It's a concept called sharding where you have multiple databases that have exactly the same tables, just filled with different data to distribute load...
Would you know if this is possible to dynamically set the resource through javascript ?
Hey All,
In case you were wondering, there is a setting in the resource setup that allows you to use an "Override DB" parameter in your resource query inside the app. If all of the databases you are querying rely on the same resource connection, you can enable this:
And then get this:
If the resources are on a different connection then it's not as straightforward, but if you or a DBA can setup a linked server to your other resources on your sever then you can directly call them in your query.
3 Likes
I also found this beta feature which should do the trick...
1 Like