Filters

Overview

Semaphor provides two types of filters for dynamic data interaction: On-Click Filter and Top-Level Filter.

• On-Click Filter: Applied when a user clicks on a chart element, such as a bar in a bar chart. By default, this action filters the X-axis field (or Y-axis for vertical charts) for subscribing cards. This filter type is useful for interactive drill-downs and cross-card filtering. Users can enable or disable this interaction in the explorer view.

• Top-Level Filter: Defined at the top of the dashboard, this filter applies either globally across all relevant cards or specifically within each dashboard sheet, depending on the configuration.

Semaphor allows the use of handlebar syntax {{ }} to apply dynamic filtering in SQL queries. There are two main filter functions:

• {{ filters | where }}: Inserts a WHERE clause to apply filtering conditions.

• {{ filters | and }}: Appends filtering conditions to an existing WHERE clause.

Example Usage

For instance, the card SQL below counts orders by cities.

SELECT city, COUNT(*) FROM sales_data
{{ filters | where }}
GROUP BY city
ORDER BY city DESC LIMIT 50

You can pass this filter either by clicking the chart element or selecting a value in the top-level filter. Semaphor resolves this query as below:

SELECT city, COUNT(*) FROM sales_data
WHERE city = 'Albuquerque' AND discount IN (0.2)
GROUP BY city
ORDER BY city DESC LIMIT 50

Notice that {{ filters | where }} placeholder is replaced by a WHERE clause, which performs AND operation on the incoming city and discount filter values. You can use this filter when your card SQL DOES NOT already contain a WHERE clause.

If your query already contains a WHERE clause, you can use the {{ filters | and }} function to append the incoming filter values to the existing WHERE clause.

SELECT city, COUNT(*) FROM sales_data
WHERE city = 'Albuquerque' {{ filters | and }}
GROUP BY city
ORDER BY city DESC LIMIT 50

Now, when the user selects 20% discount from the top-level filter, the query resolves as the following:

SELECT city, COUNT(*) FROM sales_data
WHERE city = 'Albuquerque' AND discount IN (0.2)
GROUP BY city
ORDER BY city DESC LIMIT 50

Note that the discount field is appended to the existing WHERE clause.

Internally, Semaphor represents filters as shown below. You can view the active filters by clicking on the filter icon on the top right side of the query editor.

[
  {
    "filterId": "238861f3-9721-4217-bea7-5778387b1095",
    "name": "city",
    "operation": "in",
    "values": [
      "Albuquerque",
      "Alexandria",
    ]
  },
  {
    "filterId": "040ab3de-b2b4-4c0b-9871-0a4d21ceee42",
    "name": "segment",
    "operation": "in",
    "values": [
      "Corporate"
    ]
  }
  {
    "filterId": "0345ab3de-b2b4-4c0b-9871-0a4d21ceee35",
    "name": "discount",
    "operation": ">",
    "values": ["0.2"]
  }
]

We will use the following fitler state as an example to illustrate filtering by specific fields and excluding fields.

Filtering by specific fields

If you want to apply filter only when a specific filed is present, you can reference the field value directly in the {{ filters['field']| where }} syntax. For instance, if you want the card to only filter when discount field is present, you can structure your query like this:

SELECT city, COUNT(*) FROM sales_data
{{ filters['discount']| where }}
GROUP BY city
ORDER BY city DESC LIMIT 50

Semaphor resolves the query as below, omitting segment and city columns.

SELECT city, COUNT(*) FROM sales_data
WHERE discount > 0.2
GROUP BY city
ORDER BY city DESC LIMIT 50

While doing joins, you may need to pass in the appropriate table aliases for specific fields when there is field overlap between the tables. Here's how you can do that:

SELECT city, COUNT(*) FROM sales_data
{{ filters['discount'] | where('>', table_alias='c') }}
GROUP BY city
ORDER BY city DESC LIMIT 50

Now, when the user fitlers by discount the query resolves as below:

SELECT city, COUNT(*) FROM sales_data
WHERE c.discount > 0.2
GROUP BY city
ORDER BY city DESC LIMIT 50

Excluding Fields

If you want to subscribe to all filters except specific ones, you can specify exclusions using a comma-separated list. The card SQL below subscribes to all filter fields except discount.

SELECT city, COUNT(*)
FROM sales_data {{ filters | where(exclude=['discount']) }}
GROUP BY city
ORDER BY city DESC LIMIT 50

Note that discount field is absent from the WHERE clause.

SELECT city, COUNT(*) FROM sales_data
WHERE city IN ('Albuquerque', 'Alexandria') AND segment IN ('Corporate')
GROUP BY city
ORDER BY city DESC LIMIT 50

Disable Filtering

If you want to completely disable filtering for a specific card, use {{ no_filters }}. This will unsubscribe the card from all incoming filters.

API Filters

When using an API connection, you can dynamically insert filter values into query parameters using handlebar notation. For example, consider the following API where you want to fetch the status of a todo ID based on the filter value.

https://jsonplaceholder.typicode.com/todos/{{ filters['id']['values'][0] }}

If the user selects 2 from top-level filter, the expression would resolve to:

https://jsonplaceholder.typicode.com/todos/2

Now, if you want to provide a default value when no filter is present, you can use the following expression:

https://jsonplaceholder.typicode.com/todos/{{ filters.get('id', {}).get('values', [1])[0] }}

.get('values', [1])[0] part of the expression applies the default value condition, and the expression resolves to:

https://jsonplaceholder.typicode.com/todos/1

Notice that todos/1 is the default value.

Applying Default Filters

When you load a Semaphor dashboard in your application, you can pass in the default filters to be applied to the dashboard.

You can copy the filter values array by clicking on the filter icon from the console as shown below:

Pass this array to the defaultFilterValues prop to load the dashboard with the default filters applied.

import { useDashboardActions, Dashboard } from 'semaphor';
 
const { setFilterValues } = useDashboardActions();
 
const defaultFilterValues: TFilterValue[] = [
  {
    filterId: '492ca81f-2a85-4fdd-99c7-633ada500da2',
    connectionType: 'database',
    name: 'sales_data.category',
    valueType: 'string',
    operation: 'in',
    values: ['Furniture'],
  },
];
 
<Dashboard
  onFilterValuesChange={handleFilterValuesChange}
  defaultFilterValues={defaultFilterValues}
/>;

The onFilterValuesChange function is called when the user changes the filter values. You can use this callback to update the defaultFilterValues prop and re-render the dashboard with the new filters applied.

If you don't want to reload the entire dashboard when the filter values change, you can use the setFilterValues function to update the filter values. This will apply the new filters within the same dashboard session.