Universal Search Filter Syntax
The Universal Search Filter (also mentioned as USF) is the technical syntax, provided to developers, to define a filter in Dolibarr ERP CRM application.
When to use the USF
It is used by developers to:
- build rules to filter some combo list, defined when creating a new object or property (into the property ->fields of an object class file)
- build condition for some properties of extrafields (set into the page to edit extrafields).
- to provide filters to some functions and methods into Dolibarr internal code.
- to provide filters to some Ajax components.
- to define filters on requests of APIs.
- to propagate securely filters in some tools, for example in the customreport.php tools.
In old version, all this use cases were using a different syntax. Transition was done to use the same one everywhere, between v17 and was completed in v20 (see next chapter why).
Why to use the USF
The USF syntax has been introduced for two main reason:
- To provide a secured way to propagate a filter from a component to another one, with no risk of injections.
- To provide a generic solution to define a filter that follows structured rules making possible to convert it easily and programmaticaly into the expected language, whatever is this language (most often in SQL but also in PHP or Javascript).
Syntax of USF
A simple test in USF syntax is forged this way: Open parenthesis, then the first operand that is the field, then the character ":", then the operator, then the character ":", then the second operand that is the value, then the closing parenthesis.
- The first operator allows only the following characters: a to z, 0 to 9, _, - and .
- The operator can be: =, <, >, <=, >=, !=, in, notin, like, notlike, is, isnot (and nothing else).
- If the value is a string, you must add simple quotes. If value is a date, it must use the international format into simple quotes ('YYYY-MM-DD' or 'YYYY-MM-DD HH:MM:SS') in the timezone of the server.
When using the "in" or "notin" operator, the character "," must be used to separate each possible value, the character "," can't be used inside the value. When using the operator "is" or "isnot", the only allowed value is "null".
Then, you can combine any simple test using parenthesis and OR or AND to forge combined tests.
Example of simple tests:
(myfield:=:'mystringvalue')
(myfield:>=:123.45)
(t.mydatefield:>:'2024-01-01 10:00:00')
(myfield:like:'asubstring')
(myfield:isnot:null)
(myintfield:in:1,2,3,4,5)
(mystringfield:in:'1','2','3','4','5')
Example of combined tests:
((myfield:=:'mystringvalue') OR (myfield2:>=:myintorfloatvalue))
((myfield:=:'mystringvalue') OR ((myfield2:>=:myintorfloatvalue) AND (myfield3:>:'2024-01-01 10:00:00')))
Convert USF in SQL
Dolibarr provide a method to validate, and convert an USF into a sanitized SQL string:
$errorstring = '';
$sqlcode = forgeSQLFromUniversalSearchCriteria('string_with_usf_syntax', $errorstring);
if (!$errorstring) {
// Here the $sqlcode is a valid SQL "where" string
} else {
// Here the $sqlcode is not valid
}
If the USF has a wrong syntax, the function will return the error into the $errorstring variable.