Initialization options

Typically defined on the root of the filter tree, the column schema defines the columns available to all descending conditionals in the tree. At a minimum each entry in the list defines a column name. This is a menuItem object, which means it could simply be a string. To organize columns into groups (shown as an optgroup in column drop-downs), this can also be an array of such objects (or strings). Specifying a string is very limiting; specifying an object allows defining the following additional properties on the column:

  1. alias A string used for display purposes in the UI instead of the raw column name.
  2. type The type of the column data for collation purposes. That is, the filtering algorithm attempts to convert the data to this type before comparing. If conversion fails on any row, it falls back to string comparison (for that row only).
  3. opMenu Operator list (see below).

Each terminal node in the filter tree (i.e., each conditional expression) requires a list of operators to display in it's operator drop-down. This list can come from several sources. In priority order, these sources are:

  1. The opMenu property, if defined on the column* in the node's column schema array.
  2. The node's typeOpMap hash, if defined. This would be the property in typeOpMap named for the type property if defined on the column.*
  3. The node's opMenu array, if defined.
  4. The default operator menu, as defined in conditionals.defaultOpMenu.
* The phrase the column as used above refers to the element in the node's column schema array named for the currently selected column.
— a menuItem array for columns
— a hash of menuItem arrays for various types
— a menuItem array for operators

Format: JavaScript object literal syntax

SQL WHERE clause describing filter expression
Test the filter expression against this dataRow

Format: JavaScript object literal syntax
click
here!
 ←
examples:
50
< 50
 >=A and <=L 

Filter cell parsing options: Recognize column names by ()

The Hypergrid filter tree has two main branches (or subtrees),
one for the table filters and one for the column filters.

These filter subexpressions are both required (AND’d together), resulting in a subset of qualified rows which have passed through both filters. It's called a tree because it contains both branches and leaves. The leaves represent conditional expressions (or simply conditionals). The branches, also known as subtrees, contain leaves and/or other branches and represent subexpressions that group conditionals together. Grouped conditionals are evaluated together, before conditionals outside the group.

Table filter

The table filter can be viewed in the Query Builder or as SQL WHERE clause syntax. Both interfaces manipulate the same underlying filter data structure.

Query Builder
SQL syntax

SQL WHERE clause syntax with certain restrictions.

  • Simple expressions
    • All simple expressions must be of the form column operator literal or column operator identifier. That is, the left side must refer to a column (may not be a literal).
    • Column names may be quoted with the currently set quote characters (typically double-quotes). If unquoted, they must consist of classic identifier syntax (alphanumerics and underscore, but not beginning with a numeral).
    • All literals must be quoted strings (using single quotes). (In a future release we expect to support unquoted numeric syntax for columns explicitly typed as numeric.)
  • Compound expressions
    • Compound expressions are formed by connecting simple expressions with the logical operators AND or OR.
    • However, all logical operators at each level in a complex expression (each parenthesized subexpression) must be homogeneous, i.e., either AND or OR but not a mixture of the two. In other words, there is no implicit operator precedence; grouping of expressions must always be explicitly stated with parentheses.
    • The unary logical operator NOT is supoorted before parentheses only. While the Query Builder and the Column Filter allow they syntax … NOT operator … (where operator is IN, LIKE, etc.), these must be expressed here with parenthethes: NOT (… operator …).
    • While the Query Builder and Column Filter syntax support the pseudo-operators NOR and NAND, in SQL these must be expressed as NOT (… OR …) and NOT (… AND …), respectively.
    • The Query Builder and Column Filter syntax also support the pseudo-operators STARTS abc, ENDS xyz, and CONTAINS def. These are expressed in SQL by LIKE 'abc%', LIKE '%xyz', and LIKE '%def%', respectively.
Column filters

All column filters are AND’d together. Each grid row is first qualified by the table filter and then successively qualified by each column filter subexpression.

Query Builder
SQL syntax

    Column Filter syntax

    Column filters use a simplified, compact syntax, intended to keep things short and simple and self-evident. The downside of course it is not as flexible or concise as using the Query Builder or SQL syntax.

    • Simple expressions
      • All simple expressions take the form operator literal or operator identifier. The (left side) column is always implied and is the same for all simple expressions in a compound expression. This is because column filters are always tied to a known column.
      • If the operator is an equals sign (=), it may be omitted.
      • Besides operators, no other punctuation is permitted, meaning that no quotation marks and no parentheses.
      • If a literal exactly matches a column name or alias, the operand is not taken literally and instead refers to the value in that column. (There are properties to control what constitutes such a match: Column name, alias, or either; and the case-sensitivity of the match.)
      • As literals are unquoted, any operator symbol or operator word (including logical operators for compound expressions) terminates a literal.
      • An important corollary to the above features is that operators may not appear in literals.
    • Compound expressions
      • Compound expressions are formed by connecting simple expressions with the logical operators AND, OR, NOR, or NAND ("not and").
      • However, all logical operators used in a compound column filter expression must be homogeneous. You may not mix the above logical operators in a single column. (If you need to do this, create a table filter expression instead.)
    • Hidden logic
      • If the column is also referenced in a table filter expression (on the left side of a simple expression), the column filter is flagged in its grid cell with a special star character. This is just a flag; it is not part of the syntax. Not yet implemented.

      Column filter cell syntax error