Create a filter

Combining Query node and Interactions, you can allow users to filter data based on their input. This tutorial will show you how to use Form to collect user's input, use interaction to set SQL parameters in query nodes and filter a table.

Examples in this tutorial will reference table company_daily_stock_price containing stock information, you'll find the table in the sample Postgres database.

company_daily_stock_price

Setting up your query node

In order to use user inputs to change SQL parameters to filter your data, you'll need to add parameters to your node. The syntax to add parameters varies slightly based on the type of data node you are using. See Query for syntax details and more information on parameters.

Consider the data type output by the element triggering the filter. For example, a Switch element produces a boolean value, or a Multiselect produces an array. When initializing parameters, you'll want the initialized value to be of the same data type.

When building your query, you'll also want to make sure that the data type of the parameter matches the column that it's filtering. Your statement should handle any necessary type casting or additional processing.

The following examples are all queries made in PostgreSQL query nodes. To create such a query node, locate the company_daily_stock_price in table node list and create a query node from it.

Create the query node from company_daily_stock_price table node

Example: Numerical and string inputs

This example shows how to deal with numerical and string columns.

It will pull all records where total_volume is greater than or equal to the user's input for min_volume, and where exchange_short matches what the user selects. The query was built and parameters were initialized so all data is shown before the user selects any filters.

Query:

SELECT ticker, company_name, exchange_short, volume, close, change 
FROM company_daily_stock_price
WHERE volume >= {{min_volume}}
AND exchange_short LIKE CONCAT('%','{{exchange}}','%');

Parameter initialization (See how to set parameter in #1.-add-new-parameter):

{
  "min_volume": 0,
  "exchange": ""
}

The parameter min_volume is initialized to 0, a numerical value that can be compared against the column total_volume. exchange is initialized as an empty string, and in the query, turned into a Regex expression using the CONCAT() function. This allows all records to be pulled when the string is empty, and will match specific values when the parameter value is changed.

Example: Array input

Some form elements output arrays. For example, let's say we plan on having a Multiselect input element where I allow users to choose which ticker values they'd like to see data for, and display all rows where the ticker matches.

Query:

{% set comma = joiner() %}

SELECT ticker, company_name, exchange_short,  volume, close, change 
FROM company_daily_stock_price
WHERE 

{% if ticker_list | length > 0%} 
ticker IN ({% for ticker_item in ticker_list %}{{ comma() }} '{{ticker_item}}' {% endfor %}) 
{% else %} TRUE
{% endif %};

Parameter initialization:

{
  "ticker_list": [],
  "ticker_item": ""
}

This example shows how you can pull all records for the tickers that the user has selected. In this statement, if ticker_list is not empty, we use a for loop to reconstruct the array in the right format and check if ticker appears in it. If the array is empty, all records will be displayed.

Create your elements for filtering

A common way to create a filter is to insert Form Elements into a Custom Form. The form can then collect the inputs and use them to change your SQL parameters.

Following from the data node examples above, we've created a form, which contains an Input element to collect the minimum total volume, a Radio Button to select an exchange, and a Multiselect to select tickers.

Each form element has a form item name, which is the name that the form will use to access the user input. They're also configured based on the specifications in our query.

• Minimum volume: The total_volume column is numerical, so we'll want to process the input as a number.
• Exchange: Recall that in our query, we display all records when the exchange parameter is an empty string. So when a user selects the "All" option, the value we're changing the parameter to is empty.
• Tickers: Add ticker values for the user to choose from. Either enter the value:label pairs into the input boxes, or click on </> under Options to use an accessor to an array of objects with "label" and "value" keys.

Create the interaction to change SQL parameters

Once the form elements and data node have been set up, the last step is to create the interactions that will change the SQL parameters.

What the form will do when it's submitted

Add interactions to change SQL parameters on the form element when it is submitted.

Add a Submit event block to the form, and create an action. Select API Service -> Set SQL Parameter. Make sure you're setting parameters under the right data node.

Access the user inputs from the event payload using ${event.form.min_volume} and ${event.form.exchange}.

Similarly, the interaction to filter tickers can be accessed with ${event.form.ticker_list}.

Submitting the form

We've set up the interactions to change SQL parameters upon submission of the form. However, we still need to create a way to submit the form.

On an element, such as a button, set up an interaction to submit the form.