31,003
edits
Universal Search Filter Syntax: Difference between revisions
Jump to navigation
Jump to search
m
→Syntax of UFS
m (→Syntax of UFS) Tag: 2017 source edit |
m (→Syntax of UFS) Tag: 2017 source edit |
||
| Line 24: | Line 24: | ||
* 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). | * 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 | = Syntax of USF = | ||
A simple test in | A simple test in USF syntax is forged this way: Open parenthesis, then the first operand that is the field, then :, then the operator, then :, 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 first operator allows only the following characters: a to z, 0 to 9, _, - and . | ||
* The operator can be: =, <, >, <=, >=, !=, in, notin, like, notlike, isnull, isnotnull and nothing else. | * The operator can be: =, <, >, <=, >=, !=, in, notin, like, notlike, isnull, isnotnull (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 (YYYY-MM-DD or YYYY-MM-DD HH:MM:SS) in the timezone of the server and be quotes too. | * If the value is a string, you must add simple quotes. If value is a date, it must use the international format (YYYY-MM-DD or YYYY-MM-DD HH:MM:SS) in the timezone of the server and be quotes too. | ||
| Line 50: | Line 50: | ||
((myfield:=:'mystringvalue') OR (myfield:>=:myintorfloatvalue)) | ((myfield:=:'mystringvalue') OR (myfield:>=:myintorfloatvalue)) | ||
((myfield:=:'mystringvalue') OR ((myfield:>=:myintorfloatvalue) AND (myfield:>:'2024-01-01 10:00:00'))) | ((myfield:=:'mystringvalue') OR ((myfield:>=:myintorfloatvalue) AND (myfield:>:'2024-01-01 10:00:00'))) | ||
</syntaxhighlight> | |||
= Convert USF in SQL = | |||
Dolibarr provide a method to validate, and convert an USF into a sanitizer SQL string: | |||
<syntaxhighlight lang="PHP"> | |||
$sqlcode = forgeSQLFromUniversalSearchCriteria('string_with_usf_syntax') | |||
</syntaxhighlight> | </syntaxhighlight> | ||