Filter Compilation System

Build a filter SQL compilation module that translates filter rules into SQL WHERE clauses for different data types. The system should handle string, number, date, and boolean filters with various operators, generating appropriate SQL for each filter type.

Requirements

String Filters

Implement a function that compiles string filter rules into SQL:

• EQUALS operator: Generate IN clause with quoted values (supports multiple values)
• NOT_EQUALS operator: Generate NOT IN clause with NULL handling
• INCLUDE operator: Generate LIKE pattern for case-insensitive substring matching (use LOWER() function)
• NOT_INCLUDE operator: Generate NOT LIKE pattern with AND logic for multiple values
• STARTS_WITH operator: Generate LIKE pattern with trailing wildcard
• ENDS_WITH operator: Generate LIKE pattern with leading wildcard
• NULL/NOT_NULL operators: Generate IS NULL / IS NOT NULL checks

The function should accept: dimension SQL expression, filter rule with operator and values, and a string quote character for the target database.

Number Filters

Implement a function that compiles number filter rules into SQL:

• EQUALS operator: Generate IN clause with numeric values
• NOT_EQUALS operator: Generate NOT IN clause with NULL handling
• Comparison operators (GREATER_THAN, LESS_THAN, GREATER_THAN_OR_EQUAL, LESS_THAN_OR_EQUAL): Generate appropriate comparison SQL
• IN_BETWEEN operator: Generate range check with >= and <= for two values
• NOT_IN_BETWEEN operator: Generate exclusion range with < OR >
• NULL/NOT_NULL operators: Generate IS NULL / IS NOT NULL checks

Validate all numeric values and throw an error for invalid numbers (NaN or Infinity).

Date Filters

Implement a function that compiles date filter rules into SQL with warehouse-specific casting:

• EQUALS operator: Generate equality check with date value
• NOT_EQUALS operator: Generate inequality check with NULL handling
• Comparison operators: Generate appropriate date comparisons
• IN_BETWEEN operator: Generate date range check
• Relative time operators (IN_THE_PAST, IN_THE_NEXT): Generate date arithmetic based on unit of time (days, weeks, months) with support for "completed" period mode
• IN_THE_CURRENT operator: Generate current period check using timezone-aware date boundaries
• NULL/NOT_NULL operators: Generate IS NULL / IS NOT NULL checks

The function should handle warehouse-specific SQL generation. For Trino, dates should be wrapped with CAST('value' AS timestamp). For other warehouses, use quoted strings like ('value').

Boolean Filters

Implement a function that compiles boolean filter rules into SQL:

• EQUALS operator: Generate equality check with boolean value (TRUE/FALSE)
• NOT_EQUALS operator: Generate inequality check with NULL handling
• NULL/NOT_NULL operators: Generate IS NULL / IS NOT NULL checks

Implementation Notes

• All functions should return valid SQL WHERE clause expressions (without the WHERE keyword)
• Empty or missing filter values should typically return 'true' to avoid filtering
• String quote characters vary by database (e.g., ' for most, " for some)
• Date filter functions should accept timezone and date formatting utilities
• Use moment-timezone or similar libraries for date arithmetic

Dependencies { .dependencies }

@lightdash/common { .dependency }

Provides filter compilation utilities and type definitions for the Lightdash BI platform.

Tests

Test 1: String filter with INCLUDE operator { .test }

Input:

• Dimension SQL: users.email
• Operator: INCLUDE
• Values: ['example.com', 'test.org']
• String quote char: '

Expected SQL:

(LOWER(users.email) LIKE LOWER('%example.com%')
  OR
  LOWER(users.email) LIKE LOWER('%test.org%'))

Test 2: Number filter with IN_BETWEEN operator { .test }

Input:

• Dimension SQL: orders.amount
• Operator: IN_BETWEEN
• Values: [100, 500]

Expected SQL:

(orders.amount) >= (100) AND (orders.amount) <= (500)

Test 3: Date filter with warehouse-specific casting { .test }

Input:

• Dimension SQL: orders.created_at
• Operator: GREATER_THAN
• Value: Date('2023-01-01')
• Warehouse: Trino

Expected SQL:

(orders.created_at) > CAST('2023-01-01' AS timestamp)

Input (same but different warehouse):

• Warehouse: PostgreSQL

Expected SQL:

(orders.created_at) > ('2023-01-01')

Test 4: Boolean filter with EQUALS operator { .test }

Input:

• Dimension SQL: users.is_active
• Operator: EQUALS
• Value: true

Expected SQL:

(users.is_active) = TRUE