Skip to main content

Custom User-Defined Filters

Overview​

Custom user-defined filters in SQL queries provide dynamic data filtering based on user-defined criteria. Represented as {column_name=filter_name}, these filters allow for flexible and condition-based querying.

Functionality​

Users can create filters whose values can be strings, lists of strings, or booleans. An empty list as a filter value disables the filter, returning results as if the filter wasn't applied.

Filter Behavior​

  • String Value: Applies a single condition.
  • List of Strings: Matches any of the values in the list.
  • Boolean Value: Applies a true/false condition.
  • Empty List: Disables the filter.

Syntax​

SELECT [column1, column2, ...] FROM [table] WHERE {column_name=filter_name};
  • [column1, column2, ...]: Columns to be selected.
  • [table]: The table for data selection.
  • {column_name=filter_name}: User-defined filter applied to column_name.

Usage Examples with Compiled SQL​

Example 1: String Value Filter​

User Query:

SELECT name, age FROM students WHERE {city=city_filter};

Compiled SQL:

SELECT name, age FROM students WHERE city = 'SpecificCity';

Behavior: Filters students from 'SpecificCity', as defined in city_filter.

Example 2: List of Strings Filter​

User Query:

SELECT name, age FROM students WHERE {program=program_filter};

Compiled SQL:

SELECT name, age FROM students WHERE program IN ('Program1', 'Program2');

Behavior: Filters students enrolled in 'Program1' or 'Program2', as defined in program_filter.

Example 3: Boolean Value Filter​

User Query:

SELECT name, age FROM students WHERE {enrolled=enrollment_filter};

Compiled SQL:

SELECT name, age FROM students WHERE enrolled = TRUE;

Behavior: Filters students who are enrolled, as indicated by enrollment_filter.

Example 4: Disabled Filter (Empty List)​

User Query:

SELECT name, age FROM students WHERE {city=city_filter};

Compiled SQL:

SELECT name, age FROM students WHERE 1 = 1;

Behavior: If city_filter is an empty list, the filter is disabled, and all students are selected.