Filtering Cards

Overview

Semaphor supports two types of filters: On-click Filter and Top-level Filter.

The On-click filter is applied when a user clicks a chart element, such as a bar in a bar chart. By default, the on-click action filters by X-axis field/value (and Y-axis for vertical orientation) for subscribing cards. This filter type is ideal for cross-card filtering and drill-downs. You have the option to enable or disable this interaction in the explorer view.

The Top-Level filter is defined at the top of the dashboard. It becomes a part of your dashboard definition. It is best suited for filtering by continuous data types, such as dates and numbers.

You can set up a filter subscription for your cards using a handlebar syntax {{ }}. Currently, we offer two filter functions, {{ filters | where }} and {{ filters | and }} as a shorthand for the WHERE and AND clauses.

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 }} function 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.

However, 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. We will use the following fitler array as an example to illustrate how to further finetune your filters.

[
  {
    "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"]
  }
]

Filtering by specific fields

If you only want to filter by specific fields, you can reference the field value like {{ filters['field']| where }}. For instance, if you want the card to only filter when discount field is passed, 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 this 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

You many also have a use case where you need to subscribe to all filters except for specific fields. Ycan provide a comma-separated exclusion list. The below card SQL 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

The above query resolves as follows. 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

To disable filtering, annotate your card query with {{ no_filters }}. This will unsubscribe the card from all incoming filters.

API Filters

When using an API connection, you can use the filters in the SQL query as described above. However, you can also use handlebar notation to pass query parameters or replace certain parts of the URL based on the user filters. 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.