5
edits
Universal Search Filter Syntax: Difference between revisions
Jump to navigation
Jump to search
Added description for how to search in the API inclusive how to search among array_options/complementary atteibutes/extrafields
Tag: 2017 source edit |
Jonbendtsen (talk | contribs) (Added description for how to search in the API inclusive how to search among array_options/complementary atteibutes/extrafields) |
||
| Line 3: | Line 3: | ||
Links below will be automatically replicated on translated pages by PolyglotBot --> | Links below will be automatically replicated on translated pages by PolyglotBot --> | ||
<!-- END interlang links --> | <!-- END interlang links --> | ||
[[Category:Admin_en]] | [[Category:Admin_en]] | ||
{{TemplateDocDevEn}} | {{TemplateDocDevEn}} | ||
| Line 10: | Line 9: | ||
The Universal Search Filter (also mentioned as USF) is the technical syntax, provided to developers, to define a filter in Dolibarr ERP CRM application. | 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 = | =When to use the USF= | ||
It is used by developers to: | 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). | *build rules to filter some combo list, defined when creating a new object or property (into the property ->fields of an object class file) | ||
* to provide filters to some functions and methods into Dolibarr internal code. | *build condition for some properties of extrafields (set into the page to edit extrafields). | ||
* to provide filters to some Ajax components. | *to provide filters to some functions and methods into Dolibarr internal code. | ||
* to define filters on requests of APIs. | *to provide filters to some Ajax components. | ||
* to propagate securely filters in some tools, for example in the customreport.php tools. | *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). | 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 = | =Why to use the USF= | ||
The USF syntax has been introduced for two main reason: | The USF syntax has been introduced for two main reason: | ||
= Syntax of USF = | *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. | 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 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). | *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". | 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". | ||
| Line 55: | Line 57: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
= Convert USF in SQL = | =Convert USF in SQL= | ||
Dolibarr provide a method to validate, and convert an USF into a sanitized SQL string: | Dolibarr provide a method to validate, and convert an USF into a sanitized SQL string: | ||
<syntaxhighlight lang="PHP"> | <syntaxhighlight lang="PHP"> | ||
| Line 68: | Line 70: | ||
If the USF has a wrong syntax, the function will return the error into the $errorstring variable. | If the USF has a wrong syntax, the function will return the error into the $errorstring variable. | ||
== Using sqlfilters in the API == | |||
Universal Search Filter Syntax can be very useful in the API to narrow down your search results. Generally the base columns for an object can be referenced as t.tosell, t.status, t.projet, ... unfortunately there is not always a 1:1 correlation with the key name in the JSON output and the column name in the database. You might also want to search array_options/Complementary attributes/extrafields, which is entirely possible, as long as you remember that if your key in the JSON output under array_options is called options_service_type, then you have to write it as ef.service_type in the sqlfilters in the API. | |||