Overview

To enable users to directly set up the OData filters using the Visualizer Module settings UI, we're developing a simplified syntax for defining the filters. Said syntax must be human readable and will be directly transformed into its OData counterpart to be saved in the ModuleSettings table. 

Affected version

DNN Version 9.1.1 - 9.x

Requirements

Content Editor or higher permissions.

Information 

Query filter syntax

A query can be composed of one or more simple expressions A operator B joined using logical operators AND, OR, NOT and enclosed in parentheses. First, here's a more complex example:

contentName starts with [name] and (any contentTag equals "PC" or any contentTag equals "mac")

       3. Transforming operators

      4. Formal grammar:

Here is the grammar of the query DSL(Domain Specific Kanguage) written in Augmented Backus-Naur Form. :

 


QueryFilter = Expression 1*LWSP *(LogicalOperator 1*LWSP Expression)

Expression = "(" QueryFilter ")" / RootExpression

LogicalOperator = "and" / "or" 

 

RootExpression = [ AnyModifier 1*LWSP ] FieldAccessor 1*LWSP Operator 1*LWSP ( Variable / Constant )

AnyModifier = "any" / "any of"

Operator = "starts with" / ArithmeticOperator

ArithmeticOperator = [ "is" 1*LWSP ] [ "not" 1*LWSP ] "equal" [ "s" ]

ArithmeticOperator =/ "greater than" / "greater than or equal"

ArithmeticOperator =/ "less than" / "less than or equal"

 

FieldAccessor = Identifier [ "." Identifier ]

Constant = Number / StringLiteral

Number = [ "-" ] *DIGIT [ "." 1*DIGIT ]

StringLiteral = """ 1*CHAR """ / "'" 1*CHAR "'"

Variable = "[" Identifier "]"

Identifier = ALPHA *(ALPHA / DIGIT)

Processing query filters

The user can create multiple filters for a given Visualizer Instance. They will be stored as-is along other module settings. When the Visualizer Instance is saved, the service validates the syntax and also the use of fields (ie. all fields names exist in the content type) as well as correct use of data types to ensure that numeric field isn't compared to strings and date fields are used with properly formatted dates.

Here's a sequence diagram describing that interaction:

      1. Validating query against the queried Content Type

          After being successfully parsed, a query filter must be validated against the queried 
          Content Type. Here are rules governing content type fields

• Number field

  • Can only be used with numbers

  • Valid example

    • field equals 5

  • Invalid examples

    • field equals "a"

    • field equals "5"

• Date field
  
  

  • Can only be used with properly formatted ISO date.

  • Valid examples

    • date equals "2017-10-10"
      
      date less than "2018-01-01T10:20:10"

  • Invalid examples

    • date equals "2017/09/07

• Date field (sub-type time)

  • Can only be used with a properly formatted time string

  • Valid examples

    • time less than "10:10:00"

    • time equals "12:00"

  • Invalid examples

    • time equals "99:00"
      
      time less than "noon"

• Reference object field
  
  
  • Equality operators cannot be used with reference object fields.

  • Only slug is allowed when querying reference object fields.

  • Single reference can use dot notation to access slug.

  • Multiple references must use any (of) modifier.

  • starts with cannot be used with multiple.

  • Valid examples

    • singleRef.slug equals "my-page"

    • any multipleRef.slug equals "my-page"

  • Invalid examples

    • singleRef equals "some id"
      
      multipleRef.slug equals "my-page"
      
      singleRef.name equals "Tomasz"
      
      any multipleRef.slug starts with "my-page

• Multiple choice field

  • Can only be queried with conjunction the any of modifier.

  • Cannot be used with starts with

  • Valid examples 

    • any of choices equal "YES"

    • any engineType equals "diesel"

• Invalid examples

  • engineType equals "diesel"

  • any of choices starts with "medium"

     2. Extracting query string parameters

       The query syntax allows two types of values to be used: constants and variables. The latter are alphanumeric identifiers enclosed in square brackets. For example, the expression below uses a variable name.

contentName starts with [name]

When such variable is parsed it will be transformed into a query string variable in the OData filter definition as defined on this page.

[{"QueryStringParams": ["name"], "ODataFilterQueryFormat": "name eq '[name]'"}]

Note that a variable must not be enclosed in quotes.

If a query is written as follows:

threadTitle starts with "[OT]"

Then the value [OT] will be used as a plain string literal, and will not generate a query string parameter:

[{"QueryStringParams": [], "ODataFilterQueryFormat": "details/threadTitle eq '[OT]'"}]