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.

Best Practices

Semaphor provides powerful free-form query capabilities, offering flexibility beyond drag-and-drop tools. While this flexibility gives you more control, it also requires some attention to query structure to ensure your dashboards are:

• Reliable and performant
• Properly isolated between tenants
• Easy to maintain and debug

This section covers essential best practices that will help you write better Semaphor queries, avoid common pitfalls, and make the most of the platform's capabilities.

1. Use Fully Qualified Table Names

Referencing tables without specifying the schema can lead to ambiguity. In multi-schema environments, the database might resolve table names unexpectedly, causing errors or unintended behavior.

🚫 Example of an ambiguous query

SELECT * FROM users;

Even though this query is functionally correct, and speeds up ad-hoc analysis, but may not be ideal if the card subscribes to dashboard filters.

⚠️ Issues with this approach

• In multi-schema environments, it's unclear which schema the users table belongs to.
• Semaphor may not be able to resolve the correct tenant table for multi-tenant dashboards.
• Less context for AI assistant to provide refinements and helpful suggestions.

✅ Better

SELECT * FROM my_schema.users;

Why This Matters:

• Prevents ambiguity. Ensures the correct table is referenced.
• Supports multi-tenancy. Avoids issues with schema-based data separation.
• Improves maintainability. Makes the query more readable and predictable.
• Optimizes performance. Helps Semaphor's query parser work more efficiently.

2. Use Table Aliases

When no alias is used, Semaphor applies the fully qualified column name in the WHERE clause. While this works for most databases, some—like BigQuery—do not allow fully qualified column names in WHERE conditions.

🚫 Without Table Alias

SELECT order_id, product_name FROM sales.sales_data {{ filters | where }};

If you apply a filter (country = 'US'), Semaphor generates a query with the fully qualified column name sales.sales_data.country = 'US' in the WHERE clause.

 
SELECT order_id, product_name FROM sales.sales_data
WHERE sales.sales_data.country = 'US';

This works fine for most databases, but some databases, for example like BigQuery will not allow you to use the fully-qualified-column-name in the WHERE clause.

✅ Better (With Alias)

Use table aliases to simplify queries and ensure compatibility across different databases.

 
SELECT order_id, product_name FROM sales.sales_data AS s {{ filters | where }};

Now, when you apply the filter, Semaphor generates a query with the alias s in the WHERE clause s.country = 'US'.

 
SELECT order_id, product_name FROM sales.sales_data AS s
WHERE s.country = 'US';

Why This Works:

• The alias(s) replaces the fully qualified column name in WHERE clause, ensuring compatibility across different databases.
• Prevents errors in databases that don't support fully qualified column names in WHERE clause.

3. Use Data Models to Simplify Queries

If you find yourself making the same joins over and over again, you can create a data model that pre-joins the tables you need.

🚫 Example (Complex Query with Repeated Joins)

SELECT * FROM sales.users as u
JOIN sales.orders as o ON u.id = o.user_id
JOIN sales.products as p ON o.product_id = p.id {{ filters | where }};

⚠️ Issues with this approach: The query is long and repetitive. Every time you need similar data, you must rewrite the joins. This leads to complex, repetitive logic that is harder to read and maintain.

✅ Better (Using a CTE for a Data Model)

WITH user_orders AS (
  SELECT * FROM sales.users as u
  JOIN sales.orders as o ON u.id = o.user_id
)
SELECT * FROM user_orders;

Why this is better:

• The user_orders CTE (Common Table Expression) pre-joins the required tables.
• Queries become simpler and easier to maintain.
• The same logic is reusable without repeating joins.

Using Data Models in Semaphor

You can create a data model in the semaphor console. Once created, you can reference the data model under the dm namespace:

SELECT * FROM dm.user_orders {{ filters | where }};

Benefits of Data Models:

• Improves efficiency by avoiding redundant joins.
• Enhances maintainability—update the data model once, and all queries using it benefit.
• Enables cleaner SQL, making queries more readable.