QueryFilter

Understanding Query Filters in RestBI

A QueryFilter is used to apply specific criteria to a query, allowing you to narrow down the data returned based on certain conditions. Filters are essential for refining your queries to only include the most relevant data.

Structure of a QueryFilter

  • column: string

    • The name of the column or formula to apply the filter to. This can be any column or formula defined in your model.

  • operator: string

    • The comparison operator used to filter the data. Common operators include:

      • =: Equals

      • !=: Not equals

      • >: Greater than

      • <: Less than

      • >=: Greater than or equal to

      • <=: Less than or equal to

      • LIKE: Used for pattern matching in strings (e.g., %Smith% matches any string containing "Smith").

      • IN: Used to check if a value is within a list of values (e.g., IN ('value1', 'value2')).

  • value: string | number | boolean | Date | string[] | undefined

    • The value to compare the column’s data against. This value must be of the correct type that corresponds to the column's data type.

      • For IN operators, this should be an array of values.

      • For LIKE operators, this should be a string with wildcard characters (%).

      • For date comparisons, this should be a Date object.

      • For boolean comparisons, this should be true or false.

Example QueryFilters

Here’s how you might define some query filters:

const filters: QueryFilter[] = [
    { column: 'TotalDue', operator: '>', value: 1000 },
    { column: 'CustomerName', operator: 'LIKE', value: '%Smith%' },
    { column: 'OrderDate', operator: '>=', value: new Date('2023-01-01') },
    { column: 'OrderDate', operator: '<', value: new Date('2024-01-01') },
    { column: 'ProductID', operator: 'IN', value: ['1001', '1002', '1003'] },
];

Explanation

  • TotalDue > 1000: Filters the results to include only rows where the TotalDue is greater than 1000.

  • CustomerName LIKE '%Smith%': Filters the results to include only rows where the CustomerName contains "Smith".

  • OrderDate >= '2023-01-01': Filters the results to include only rows where the OrderDate is on or after January 1, 2023.

  • OrderDate < '2024-01-01': Filters the results to exclude rows where the OrderDate is on or after January 1, 2024.

  • ProductID IN ['1001', '1002', '1003']: Filters the results to include only rows where the ProductID is one of the specified values (1001, 1002, or 1003).

See Also

  • Tables - Explore how Tables organize Columns, the fields in your database.

  • Joins - Discover how to connect data from different Tables using Joins.

  • Formulas - Learn to create new data outputs by applying calculations to existing Columns.

  • Queries - Understand how to structure your data retrieval with Queries.

PreviousQueryNextSQLResult

Last updated