HyperDoc Query Language (HQL)

Owned by Tom Wyczółkowski
Last updated: Nov 28, 2023 by Agnieszka Siemiatycka
3 min read

HyperDoc Query Language (HQL) is used across HDC application: advanced search,presentation filters and HOPA (as one of possible locators).

Syntax

HQL statement consists of at least one expression in following form:

{property_name} {operator} {value} || {property_name} {operator}

property_name - name of the property of the given class

operator - comparison operator

value - the expected value of property



Please note that not numeric values have to be double quoted (this applies to text and dates)

 

Several expression could form complex statements with usage of logical operators: and, or. Additionally to accurately specify condition priority one could use parenthesis. Here is a sample form of complex statement for illustration:

((expression or expression) and (expression and expression or expression)) and expression

 

Please note that logical operators should be used in lower case, same applies to property names and some operators (which are not based on symbols).

 

List of operators:

Operator Name

Operator Symbol

Example

Suitable types *

Operator Name

Operator Symbol

Example

Suitable types *

Equals

=

property = 1

numeric, text, datetime and boolean

Not Equals

!=

property != "sample"

numeric, text, datetime and boolean

Not Less Than

>=

property >= 11.23

numeric

Not Greater Than

<=

property <= 12

numeric

Greater Than

>

property > 1

numeric

Less Than

<

property < 5.05

numeric

Before

before

property before"21.07.2017"

datetime

After

after

property after "2013-05-25T12:12:12.100+02:00"

datetime

In Set

in

property in ("DICT1", "DICT2", "DICT3")

numeric, text

Not In Set

not in

property not in (1, 2, 3)

numeric, text

Contains

contains

property contains "room"

text

Not contains

not contains

property not contains "room"

text

Starts With

starts-with

property starts-with "plan"

text

Not Starts With

not starts-with

property not starts-with "plan"

text

Is Empty

is empty

property is empty

numeric, text, datetime and boolean **

Is Not Empty

is not empty

property is not empty

numeric, text, datetime and boolean **



Data types:

  • Numeric types: Float, Integer, Serial, Currency, Area, Quantity, Length

  • Text types: Text, GUID

  • boolean: bool (true/false; on/off; yes/no; 0/1)

  • datetime: various date formats:

    • dd/MM/YYYY

    • dd-MM-YYYY

    • dd.MM.YYYY

    • YYYY/MM/dd

    • YYYY-MM-dd

    • YYYY.MM.dd

    • YYYY-MM-ddTHH:mm:ss.SSSXXX

  • Field values (there is a possibility to process HQL queries that compares values of two fields (numerical and string only). 




* using wrong operator doesn't end with an error unless it is strictly not possible to evaluate given expression i.e. starts-with "test" would raise error, when "text" > "Text" would be processed, but is not recommended

** for text types we not only check whether value is set, but also whether it is not an empty string



Example expressions

fi2space_areabra < 11 and fi2space_descr starts-with "test" fi2space_function in ("00", "01") or fi2space_function contains "PER" and hd_objcustom2 in ("BIA") hd_docrevdate before "2017-08-08T00:00:00.000+02:00" (fi2space_function in ("00", "01") or fi2space_function contains "PER") and hd_objcustom2 in ("BIA") ((fi2space_class is empty) or (fi2space_function is empty)) unbounded_height not in (-3.14, 0, 1.1, 2.5) and fi2space_class contains "ÖVA" fi2space_areabra is empty and fi2space_function in ("00", "01") or fi2space_function contains "PER" and hd_objcustom2 in ("BIA") or fi2space_areanta > 11 and (fi2space_class starts-with "ÖVA" or dummy_date before "2013-01-01") hd_docdescr = "Italic" fi2space_id in ("000480000900059","000480000900051","000480000900040") hd_docdate before "1990-12-01" fi2space_id contains "48000090004" fi2space_descr starts-with "RUM 2" hd_docdate before "1996-11-05" and hd_docdate after "1996-11-03" fi2space_class in ("02","ÖVA") and fi2space_function not in ("KOMMUNIKATION","03","02","00","PERSONAL","SERVICE","99","TEKNIK","01") fi2space_class is empty fi2space_areabra >= "50" and fi2space_areabra < 100 fi2space_id != "01" hd_docdescr is not empty rentalunit_occupied = "true" fi2space_descr starts-with fi2space_name fi2space_areabra > fi2space_areanta



HQL Query language support for field names

So far query language (HQL) used for filters, presentations or object search only accepted specific values.

These values could be given as string, number or date. Example: 

space_diff_bra_bta >= 1.02 and space_diff_bra_bta <= 1.05
space_diff_bra_bta >= 1.02 and space_diff_bra_bta <= 1.05
fi2space_name starts-with "test"

Now the language was extended to support also comparing values from other fields. For now numerical and string only.  Example:

space_diff_bra_bta > 1.05 and space_bra > space_bta
(space_diff_bra_bta > 1.05) and (space_bra > space_bta)

Examples

  • Automatic presentation filter example with HQL comparing two numerical fields:




  • Manual Presentation filter example with HQL comparing two text fields:




  • Auto Sum plugin with HQL condition




This was not added to advanced search interface so it can be used only there were you can type in HQL (like filters, smart fields).