Can't refresh pie chart on date range change

App Building
1

I have a pie chart that is build from the results of a SQL query as below:

image

I would now like to add the ability to filter the SQL query results by a date range.

I have added a Date Range component to the app but do not know how to proceed?

I have tried the following.

I've learnt previously that in order to pass parameters to my SQL queries I need to call them using a JS script to pass the parameters in. I have created user_stats_js_refresh_stats with additionalScope like so:

user_stats_sql_onboarding_status.trigger({
  additionalScope : {
    from: user_stats_date_range.value.start,
    to: user_stats_date_range.value.end
  }  
});

I have set up the Date Range component to call this script on Change

image

I've updated the underlying query to use the from and to dates. However changing the date range has no effect on the pie chart though.

What is the correct way to approach this problem?

2

Where is the date range being changed?

3

Just by clicking on the Date Range component and choosing a new data range.

image
image724×358 21.6 KB

That invokes the change handler
image

4

try using an query json with sql resource type doing something like the following in the

SELECT * FROM {{formatDataAsArray(yourTable.value)}}
WHERE  ((your_date_column_name >= {{ yourDateRangeField.value.start}} and value_date <= {{ yourDateRangeField.value.end}}) OR ({{yourDateRangeField.value.start == ''}} AND {{yourDateRangeField.value.end == ''}}))

And set it to run when inputs change and then use it as the source for the pie chart... this way you won't have to use additional scope

Reply

5

Thanks @ScottR . I had tried something similar before but found out it doesn't work with Postgres due to converting the query to a prepared statement.

There is a setting that can be disabled but I was a bit loathe to do that as that allows for sql injection.

See Problem with SQL query and parameters - #2 by ScottR and '{{}}' cannot be used when using the DO-END statement in SQL

Not sure where is any other way though?

6

You should be able to do this because you could do the following:
Your original query I assume is one that hits the Postgres db/MySQL db directly.... take the result of this and store it in a temp state then query yourTempState.value. I do this all of the time...
Also, try not to double post.... let's see if we can get this issue solved for you first before you post again
As you can see I do not disable converting queries...
Screen Shot 2022-09-23 at 9.02.10 AM

So, again

  1. hit your db DIRECTLY with query that uses Postgres resource.
  2. Get the data back and store in tempState
  3. Use Query JSON with SQL against the tempState
7