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 * |
|---|---|---|---|
| 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:
|
Now the language was extended to support also comparing values from other fields. For now numerical and string only. Example:
|
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).