When you set a Dropdown Control in a Chartio dashboard to Show All on the empty state, you are telling the chart’s query to apply a filter of
WHERE 1 = 1. You’re ultimately telling the chart’s query not to filter anything.
In this example, we created a Dropdown Control by clicking Add Control in the sidebar of the dashboard and selecting the Dropdown option. This example filters a subset of marketing campaigns we’re tracking in our data source that have happened within the specified date range.
Now we are going analyze what is happening in the query, we will need to click the tab that says ‘Executed query’ to see the SQL Chartio has written on the backend. In the chart that the Dropdown control affects, the query is sending
where 1=1 into the WHERE clause:
select “Marketing”.“description” as “Description”, sum(“Marketing”.“cost”) as “Total sum of Cost” from “public”.“marketing” as “Marketing” where (1 = 1) group by “Description” order by “Description” asc limit 1000;
What this is doing is once an item, or a few items are selected from the Dropdown, the query below begins to look different from the prior screenshot. This is because the query begins to filter on our marketing campaign values. In the latter screenshot, we show the filtering of more than one value; ‘Event’, ‘Adwords’, ‘Web’, and ‘Sales’. We have the ability to choose more than value in our Dropdown because we have checked the option to multi select when this Dropdown was first created:
select “Marketing”.“description” as “Description”, sum(“Marketing”.“cost”) as “Total sum of Cost” from “public”.“marketing” as “Marketing” where (“Marketing”.“description” in (‘Event’, ‘Adwords’, ‘Web’, ‘Sales’)) group by “Description” order by “Description” asc limit 1000;
The issue is that when Show All is chosen, the query is not passing through all the items; instead, the chart does not filter anything.