Query Filters and Search

Learn how to implement powerful filtering capabilities in Stack9 applications. This comprehensive guide covers all filter types, search functionality, and how they integrate with different screen types.

Overview

Query filters in Stack9 enable users to refine data displayed in list views and grids. They work with both stack9_api and opensearch connector types, providing a rich filtering experience through the GridBuilder component.

Key Concepts

Filters: UI controls that allow users to narrow down data based on specific criteria
Filter Sections: Grouped collections of related filters for better organization
Search Fields: Text-based search across multiple fields simultaneously
Filter Types: Different UI components for various data types (dropdowns, date ranges, checkboxes, etc.)

How Filters Work with Screen Types

Filters are primarily used with screens that display tabular data:

Compatible Screen Types

Screen Type	Filter Support	Implementation
`listView`	✅ Full support	Via `listQuery` with filters
`simpleCrud`	✅ Full support	Via `listQuery` with filters
`screenView`	✅ When using GridBuilder	Via custom components
`detailView`	❌ Not applicable	Single record view
`embeddedView`	✅ When using GridBuilder	Via custom components

The GridBuilder Component

The GridBuilder component (packages/stack9-ui/src/components/common/Grid/GridBuilder.tsx) renders filters defined in your queries. It provides:

Filter accordion UI on the left side
Search box at the top (when querySearchFields is defined)
Applied filter badges
Filter sections with collapse/expand functionality

Filter Schema

Filters are defined in the query library JSON files. Here's the complete schema:

{
  "filters": [
    {
      "name": "Filter Display Name",     // Required: Shows in UI
      "key": "filter_key",               // Required: Unique identifier
      "typeQueryFilter": "compare",      // Required: compare | between | array
      "field": "database_field",         // Required: Database field to filter
      "typeFilter": "FilterUIType",      // Required: UI component type
      "useSubquery": false,              // Required: For nested queries
      "sequence": 1,                     // Required: Display order
      "filterSectionKey": "section_key", // Optional: Group filters
      "dataSource": {                    // Required for selection filters
        "type": "static",               // static | query | queryDistinct
        "options": []                   // For static type
      }
    }
  ],
  "filterSections": [
    {
      "name": "Section Name",
      "key": "section_key",
      "sequence": 1,
      "collapsed": true                  // Optional: Start collapsed
    }
  ],
  "querySearchFields": ["field1", "field2"] // Text search fields
}

Filter Types Reference

Text Filters

StringCompareValue

Text input with comparison operators (equals, contains, starts with).

{
  "name": "Customer Name",
  "key": "customer_name",
  "typeQueryFilter": "compare",
  "field": "name",
  "typeFilter": "StringCompareValue",
  "useSubquery": false,
  "sequence": 1
}

Use cases: Names, descriptions, codes, addresses

Numeric Filters

NumericCompareValue

Number input with comparison operators (equals, greater than, less than).

{
  "name": "Order ID",
  "key": "order_id",
  "typeQueryFilter": "compare",
  "field": "id",
  "typeFilter": "NumericCompareValue",
  "useSubquery": false,
  "sequence": 1
}

NumericRangeValue

Min/max number inputs for range filtering.

{
  "name": "Price Range",
  "key": "price",
  "typeQueryFilter": "between",
  "field": "price",
  "typeFilter": "NumericRangeValue",
  "useSubquery": false,
  "sequence": 2
}

Use cases: IDs, quantities, prices, scores

Date Filters

DateRangeValue

Date range picker with start and end dates.

{
  "name": "Created Date",
  "key": "created_date",
  "typeQueryFilter": "between",
  "field": "created_at",
  "typeFilter": "DateRangeValue",
  "useSubquery": false,
  "sequence": 1
}

DateCompareValue

Single date picker with comparison operators.

{
  "name": "Due Date",
  "key": "due_date",
  "typeQueryFilter": "compare",
  "field": "due_date",
  "typeFilter": "DateCompareValue",
  "useSubquery": false,
  "sequence": 1
}

Use cases: Creation dates, transaction dates, deadlines

Selection Filters

SingleDropDown

Single selection dropdown menu.

{
  "name": "Card Type",
  "key": "card_type",
  "typeQueryFilter": "compare",
  "field": "card_type",
  "typeFilter": "SingleDropDown",
  "useSubquery": false,
  "sequence": 1,
  "dataSource": {
    "type": "static",
    "options": [
      { "label": "Visa", "value": "Visa" },
      { "label": "Mastercard", "value": "Mastercard" },
      { "label": "American Express", "value": "American Express" }
    ]
  }
}

MultiDropDown

Multiple selection dropdown with checkboxes.

{
  "name": "Status",
  "key": "status",
  "typeQueryFilter": "array",
  "field": "status",
  "typeFilter": "MultiDropDown",
  "useSubquery": false,
  "sequence": 1,
  "dataSource": {
    "type": "static",
    "options": [
      { "label": "Active", "value": "active" },
      { "label": "Pending", "value": "pending" },
      { "label": "Inactive", "value": "inactive" }
    ]
  }
}

Checkboxes

Checkbox group for multiple selections.

{
  "name": "Payment Method",
  "key": "payment_method",
  "typeQueryFilter": "array",
  "field": "payment_method",
  "typeFilter": "Checkboxes",
  "useSubquery": false,
  "sequence": 1,
  "dataSource": {
    "type": "static",
    "options": [
      { "label": "Credit Card", "value": "credit_card" },
      { "label": "PayPal", "value": "paypal" },
      { "label": "Bank Transfer", "value": "bank_transfer" }
    ]
  }
}

RadioButtons

Radio button group for single selection.

{
  "name": "Customer Type",
  "key": "customer_type",
  "typeQueryFilter": "compare",
  "field": "type",
  "typeFilter": "RadioButtons",
  "useSubquery": false,
  "sequence": 1,
  "dataSource": {
    "type": "static",
    "options": [
      { "label": "Individual", "value": "individual" },
      { "label": "Business", "value": "business" }
    ]
  }
}

Boolean Filter

BooleanValue

Yes/No/Any selector for boolean fields.

{
  "name": "Is Featured",
  "key": "is_featured",
  "typeQueryFilter": "compare",
  "field": "is_featured",
  "typeFilter": "BooleanValue",
  "useSubquery": false,
  "sequence": 1
}

Use cases: Active/inactive flags, true/false conditions

Data Source Types

Static Data Source

Hardcoded options defined in the query.

"dataSource": {
  "type": "static",
  "options": [
    { "label": "Option 1", "value": "value1" },
    { "label": "Option 2", "value": "value2" }
  ]
}

Query Data Source

Fetches options from another query.

"dataSource": {
  "type": "query",
  "labelProp": "name",      // Field to display
  "valueProp": "id",        // Field to use as value
  "query": {
    "name": "getCategoryList",
    "queryKey": "getcategorylist"
  }
}

Query Distinct Data Source

Gets distinct values from a query.

"dataSource": {
  "type": "queryDistinct",
  "query": {
    "name": "getDistinctStatuses",
    "queryKey": "getdistinctstatuses"
  }
}

Text Search with querySearchFields

The querySearchFields property enables text-based search across multiple fields simultaneously.

{
  "querySearchFields": [
    "name",
    "email",
    "phone",
    "address"
  ]
}

Search Field Weighting (OpenSearch)

For OpenSearch queries, you can add weight to fields using the ^ operator:

{
  "querySearchFields": [
    "name^3",         // 3x weight
    "email^2",        // 2x weight
    "description",    // 1x weight (default)
    "sku.keyword"     // Exact match on keyword field
  ]
}

Complete Examples

Example 1: Customer List with Comprehensive Filters (OpenSearch)

{
  "key": "getcustomerlist",
  "name": "getCustomerList",
  "connector": "opensearch",
  "queryTemplate": {
    "actionType": "search",
    "indexName": "customer",
    "bodyParams": "{\n  \"from\": {{page}},\n  \"size\": {{limit}},\n  \"query\": {\n    \"bool\": {\n      \"filter\": [\n        {\n          \"term\": {\n            \"is_active\": true\n          }\n        }\n      ]\n    }\n  }\n}"
  },
  "filterSections": [
    {
      "name": "Customer Details",
      "key": "customer_details",
      "sequence": 1,
      "collapsed": false
    },
    {
      "name": "Contact Preferences",
      "key": "contact_preferences",
      "sequence": 2,
      "collapsed": true
    }
  ],
  "filters": [
    {
      "name": "Customer Name",
      "key": "name",
      "typeQueryFilter": "compare",
      "field": "all_names",
      "typeFilter": "StringCompareValue",
      "useSubquery": false,
      "sequence": 1,
      "filterSectionKey": "customer_details"
    },
    {
      "name": "Email",
      "key": "email",
      "typeQueryFilter": "compare",
      "field": "email_address",
      "typeFilter": "StringCompareValue",
      "useSubquery": false,
      "sequence": 2,
      "filterSectionKey": "customer_details"
    },
    {
      "name": "Date of Birth",
      "key": "dob",
      "typeQueryFilter": "between",
      "field": "date_of_birth",
      "typeFilter": "DateRangeValue",
      "useSubquery": false,
      "sequence": 3,
      "filterSectionKey": "customer_details"
    },
    {
      "name": "Customer Type",
      "key": "customer_type",
      "typeQueryFilter": "array",
      "field": "type",
      "typeFilter": "MultiDropDown",
      "useSubquery": false,
      "sequence": 4,
      "filterSectionKey": "customer_details",
      "dataSource": {
        "type": "static",
        "options": [
          { "label": "Individual", "value": "Individual" },
          { "label": "Business", "value": "Business" }
        ]
      }
    },
    {
      "name": "Prefer Email",
      "key": "prefer_email",
      "typeQueryFilter": "compare",
      "field": "preferences.email",
      "typeFilter": "BooleanValue",
      "useSubquery": false,
      "sequence": 1,
      "filterSectionKey": "contact_preferences"
    }
  ],
  "querySearchFields": [
    "preferred_name^2",
    "last_name^2",
    "email_address",
    "phone",
    "address_line_1"
  ],
  "userParams": {
    "page": "0",
    "limit": "50"
  }
}

Example 2: Product List with Filters (Stack9 API)

{
  "key": "getproductlist",
  "name": "getProductList",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/product/search",
    "bodyParams": {
      "$select": ["id", "name", "sku", "price", "category_id", "status"],
      "$where": { "_is_deleted": false },
      "$sort": { "created_at": "desc" },
      "$withRelated": ["category", "brand"]
    },
    "queryParams": {
      "page": "{{page}}",
      "limit": "{{limit}}"
    }
  },
  "filterSections": [
    {
      "name": "Product Information",
      "key": "product_info",
      "sequence": 1
    },
    {
      "name": "Pricing & Stock",
      "key": "pricing_stock",
      "sequence": 2
    }
  ],
  "filters": [
    {
      "name": "Category",
      "key": "category",
      "typeQueryFilter": "array",
      "field": "category_id",
      "typeFilter": "MultiDropDown",
      "useSubquery": false,
      "sequence": 1,
      "filterSectionKey": "product_info",
      "dataSource": {
        "type": "query",
        "labelProp": "name",
        "valueProp": "id",
        "query": {
          "name": "getCategoryList",
          "queryKey": "getcategorylist"
        }
      }
    },
    {
      "name": "Status",
      "key": "status",
      "typeQueryFilter": "array",
      "field": "status",
      "typeFilter": "Checkboxes",
      "useSubquery": false,
      "sequence": 2,
      "filterSectionKey": "product_info",
      "dataSource": {
        "type": "static",
        "options": [
          { "label": "Active", "value": "active" },
          { "label": "Discontinued", "value": "discontinued" },
          { "label": "Out of Stock", "value": "out_of_stock" }
        ]
      }
    },
    {
      "name": "Price Range",
      "key": "price",
      "typeQueryFilter": "between",
      "field": "price",
      "typeFilter": "NumericRangeValue",
      "useSubquery": false,
      "sequence": 1,
      "filterSectionKey": "pricing_stock"
    },
    {
      "name": "In Stock",
      "key": "in_stock",
      "typeQueryFilter": "compare",
      "field": "stock_quantity",
      "typeFilter": "BooleanValue",
      "useSubquery": false,
      "sequence": 2,
      "filterSectionKey": "pricing_stock"
    }
  ],
  "querySearchFields": ["name", "sku", "description"],
  "userParams": {
    "page": "0",
    "limit": "20"
  }
}

Example 3: Sales Order List with Complex Filters

{
  "key": "getsalesorderlist",
  "name": "getSalesOrderList",
  "connector": "opensearch",
  "queryTemplate": {
    "actionType": "search",
    "indexName": "sales_order",
    "bodyParams": "{\n  \"from\": {{page}},\n  \"size\": {{limit}},\n  \"sort\": [{\"transaction_dt\": {\"order\": \"desc\"}}]\n}"
  },
  "filterSections": [
    {
      "name": "Order Details",
      "key": "order_details",
      "sequence": 1
    },
    {
      "name": "Customer Information",
      "key": "customer_info",
      "sequence": 2,
      "collapsed": true
    },
    {
      "name": "Payment Information",
      "key": "payment_info",
      "sequence": 3,
      "collapsed": true
    }
  ],
  "filters": [
    {
      "name": "Order Number",
      "key": "order_number",
      "typeQueryFilter": "compare",
      "field": "sales_order_number",
      "typeFilter": "StringCompareValue",
      "useSubquery": false,
      "sequence": 1,
      "filterSectionKey": "order_details"
    },
    {
      "name": "Transaction Date",
      "key": "transaction_date",
      "typeQueryFilter": "between",
      "field": "transaction_dt",
      "typeFilter": "DateRangeValue",
      "useSubquery": false,
      "sequence": 2,
      "filterSectionKey": "order_details"
    },
    {
      "name": "Order Type",
      "key": "order_type",
      "typeQueryFilter": "array",
      "field": "order_type",
      "typeFilter": "MultiDropDown",
      "useSubquery": false,
      "sequence": 3,
      "filterSectionKey": "order_details",
      "dataSource": {
        "type": "static",
        "options": [
          { "label": "Single Sale", "value": "single_sale" },
          { "label": "Subscription", "value": "subscription" },
          { "label": "Credit Note", "value": "credit_note" }
        ]
      }
    },
    {
      "name": "Total Amount",
      "key": "total_amount",
      "typeQueryFilter": "between",
      "field": "total_payable_amount",
      "typeFilter": "NumericRangeValue",
      "useSubquery": false,
      "sequence": 4,
      "filterSectionKey": "order_details"
    },
    {
      "name": "Customer Name",
      "key": "customer_name",
      "typeQueryFilter": "compare",
      "field": "customer.name",
      "typeFilter": "StringCompareValue",
      "useSubquery": false,
      "sequence": 1,
      "filterSectionKey": "customer_info"
    },
    {
      "name": "Customer Email",
      "key": "customer_email",
      "typeQueryFilter": "compare",
      "field": "customer.email",
      "typeFilter": "StringCompareValue",
      "useSubquery": false,
      "sequence": 2,
      "filterSectionKey": "customer_info"
    },
    {
      "name": "Payment Method",
      "key": "payment_method",
      "typeQueryFilter": "array",
      "field": "payment_method",
      "typeFilter": "Checkboxes",
      "useSubquery": false,
      "sequence": 1,
      "filterSectionKey": "payment_info",
      "dataSource": {
        "type": "static",
        "options": [
          { "label": "Credit Card", "value": "credit_card" },
          { "label": "Direct Debit", "value": "direct_debit" },
          { "label": "Cash", "value": "cash" },
          { "label": "Bank Deposit", "value": "bank_deposit" },
          { "label": "PayPal", "value": "paypal" }
        ]
      }
    },
    {
      "name": "Payment Status",
      "key": "payment_status",
      "typeQueryFilter": "array",
      "field": "payment_status",
      "typeFilter": "MultiDropDown",
      "useSubquery": false,
      "sequence": 2,
      "filterSectionKey": "payment_info",
      "dataSource": {
        "type": "static",
        "options": [
          { "label": "Paid", "value": "paid" },
          { "label": "Unpaid", "value": "unpaid" },
          { "label": "Partial", "value": "partial" },
          { "label": "Failed", "value": "failed" }
        ]
      }
    }
  ],
  "querySearchFields": [
    "sales_order_number.keyword",
    "customer_name",
    "customer_email",
    "receipt_number",
    "payment_reference"
  ],
  "userParams": {
    "page": "0",
    "limit": "25"
  }
}

Integrating Filters with Screens

Using Filters in listView Screen

{
  "head": {
    "key": "customer_list",
    "title": "Customers",
    "app": "crm",
    "route": "customers"
  },
  "screenType": "listView",
  "listQuery": "getcustomerlist",
  "querySearchFields": ["name", "email", "phone"],
  "columnsConfiguration": [
    // Column definitions
  ]
}

The screen will automatically:

Render filters from the query definition
Show search box if querySearchFields is defined
Apply filters to the query when users make selections
Update results in real-time

Using Filters in simpleCrud Screen

{
  "head": {
    "key": "product_management",
    "title": "Product Management",
    "app": "inventory",
    "route": "products"
  },
  "screenType": "simpleCrud",
  "entityKey": "product",
  "listQuery": "getproductlist",
  "detailQuery": "getproductbyid",
  "columnsConfiguration": [
    // Column definitions
  ],
  "formFieldset": {
    // Form configuration
  }
}

The simpleCrud screen:

Shows filters in the list view mode
Allows filtering before selecting records to edit
Maintains filter state when switching between list and form views

Best Practices

1. Filter Organization

Group related filters into sections for better UX:

"filterSections": [
  {
    "name": "Basic Filters",
    "key": "basic",
    "sequence": 1,
    "collapsed": false  // Keep important filters visible
  },
  {
    "name": "Advanced Filters",
    "key": "advanced",
    "sequence": 2,
    "collapsed": true   // Collapse less-used filters
  }
]

2. Filter Naming

Use clear, consistent naming:

✅ "Transaction Date" (clear)
❌ "Trans Dt" (abbreviated)
✅ "Customer Name" (specific)
❌ "Name" (ambiguous)

3. Default Values

Set sensible defaults in userParams:

"userParams": {
  "page": "0",
  "limit": "25",
  "status": "active",      // Default to active records
  "date_from": "{{last30days}}"  // Default date range
}

4. Performance Optimization

For large datasets:

Index filtered fields in the database
Use useSubquery: true for related entity filters
Limit dropdown options with intelligent queries
Consider using OpenSearch for complex filtering

5. Query Data Sources

Cache frequently-used filter options:

// Create a dedicated query for filter options
{
  "key": "getcategoryoptions",
  "name": "getCategoryOptions",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/category/search",
    "bodyParams": {
      "$select": ["id", "name"],
      "$where": { "is_active": true },
      "$sort": { "name": "asc" },
      "$limit": 100
    }
  }
}

Troubleshooting

Filters Not Appearing

Problem: Filters defined but not showing in UI

Solutions:

Verify listQuery in screen has filters defined
Check filter sequence values are unique
Ensure filterSectionKey matches defined sections
Verify GridBuilder component is being used

Filter Not Working

Problem: Filter appears but doesn't filter data

Solutions:

Check field matches database column name
Verify typeQueryFilter matches filter logic (compare/between/array)
For OpenSearch, ensure field is indexed
Check useSubquery setting for nested fields

Problem: Dropdown filter shows no options

Solutions:

For query data source, verify query returns data
Check labelProp and valueProp match query fields
Ensure query has proper permissions
For static options, verify JSON syntax

Search Not Working

Problem: Search box doesn't filter results

Solutions:

Verify querySearchFields is defined
Check fields exist in the entity/index
For OpenSearch, verify fields are searchable
Test with exact field values first

Advanced Topics

Custom Filter Components

For complex filtering needs, create custom filter components:

{
  "name": "Custom Date Filter",
  "key": "custom_date",
  "typeQueryFilter": "compare",
  "field": "date_field",
  "typeFilter": "CustomComponentFilter",
  "useSubquery": false,
  "sequence": 1,
  "options": {
    "componentName": "CustomDateFilter",
    "props": {
      "format": "YYYY-MM-DD",
      "showTime": true
    }
  }
}

Load filter options based on other filter selections:

{
  "name": "Subcategory",
  "key": "subcategory",
  "typeQueryFilter": "array",
  "field": "subcategory_id",
  "typeFilter": "MultiDropDown",
  "useSubquery": false,
  "sequence": 2,
  "dataSource": {
    "type": "query",
    "labelProp": "name",
    "valueProp": "id",
    "query": {
      "name": "getSubcategories",
      "queryKey": "getsubcategories",
      "userParams": {
        "category_id": "{{filters.category}}"  // Reference other filter
      }
    }
  }
}

Filter Persistence

Filters can be persisted in URL parameters for bookmarking:

// URL structure with filters
/customers?filters[status]=active&filters[type]=business&search=acme

Integration with Pagination

Filters work seamlessly with pagination. Always include pagination parameters:

"queryParams": {
  "page": "{{page}}",     // Current page (0-indexed)
  "limit": "{{limit}}"    // Items per page
}

The framework automatically:

Resets to page 0 when filters change
Maintains filter state during pagination
Updates total count based on filtered results

Summary

Query filters in Stack9 provide a powerful way to refine data in list views. Key takeaways:

Multiple filter types support different data types and use cases
Filter sections organize filters for better UX
Text search complements filters for quick searching
Data sources can be static, from queries, or distinct values
Integration is automatic with listView and simpleCrud screens
Performance can be optimized with proper indexing and query design

By combining different filter types, search fields, and proper organization, you can create intuitive and performant data filtering experiences for your users.

Overview​

Key Concepts​

How Filters Work with Screen Types​

Compatible Screen Types​

The GridBuilder Component​

Filter Schema​

Filter Types Reference​

Text Filters​

StringCompareValue​

Numeric Filters​

NumericCompareValue​

NumericRangeValue​

Date Filters​

DateRangeValue​

DateCompareValue​

Selection Filters​

SingleDropDown​

MultiDropDown​

Checkboxes​

RadioButtons​

Boolean Filter​

BooleanValue​

Data Source Types​

Static Data Source​

Query Data Source​

Query Distinct Data Source​

Text Search with querySearchFields​

Search Field Weighting (OpenSearch)​

Complete Examples​

Example 1: Customer List with Comprehensive Filters (OpenSearch)​

Example 2: Product List with Filters (Stack9 API)​

Example 3: Sales Order List with Complex Filters​

Integrating Filters with Screens​

Using Filters in listView Screen​

Using Filters in simpleCrud Screen​

Best Practices​

1. Filter Organization​

2. Filter Naming​

3. Default Values​

4. Performance Optimization​

5. Query Data Sources​

Troubleshooting​

Filters Not Appearing​

Filter Not Working​

Dropdown Options Empty​

Search Not Working​

Advanced Topics​

Custom Filter Components​

Dynamic Filter Options​

Filter Persistence​

Integration with Pagination​

Summary​

See Also​

Overview

Key Concepts

How Filters Work with Screen Types

Compatible Screen Types

The GridBuilder Component

Filter Schema

Filter Types Reference

Text Filters

StringCompareValue

Numeric Filters

NumericCompareValue

NumericRangeValue

Date Filters

DateRangeValue

DateCompareValue

Selection Filters

SingleDropDown

MultiDropDown

Checkboxes

RadioButtons

Boolean Filter

BooleanValue

Data Source Types

Static Data Source

Query Data Source

Query Distinct Data Source

Text Search with querySearchFields

Search Field Weighting (OpenSearch)

Complete Examples

Example 1: Customer List with Comprehensive Filters (OpenSearch)

Example 2: Product List with Filters (Stack9 API)

Example 3: Sales Order List with Complex Filters

Integrating Filters with Screens

Using Filters in listView Screen

Using Filters in simpleCrud Screen

Best Practices

1. Filter Organization

2. Filter Naming

3. Default Values

4. Performance Optimization

5. Query Data Sources

Troubleshooting

Filters Not Appearing

Filter Not Working

Dropdown Options Empty

Search Not Working

Advanced Topics

Custom Filter Components

Dynamic Filter Options

Filter Persistence

Integration with Pagination

Summary

See Also