Important

You are browsing the documentation for version 3.1 of OroCommerce, OroCRM and OroPlatform, which is no longer maintained. Read version 5.1 (the latest LTS version) of the Oro documentation to get up-to-date information.

See our Release Process documentation for more information on the currently supported and upcoming releases.

Advanced Search API¶

REST and SOAP APIs allow to create advanced search queries.

Parameters for APIs requests:

query - search string

REST API url: http://domail.com/api/rest/latest/search/advanced

SOAP function name: advancedSearch

REST API work with get request only.

Result¶

Request returns an array with data:

records_count - the total number of results (without offset and max_results) parameters
count - count of records in the current request
data - array with data. Data consists of the following values:
- entity_name - class name of entity
- record_id - id of record from this entity
- record_string - the title of this record
- record_url - the given URL for this record
- selected_data - data from fields that have been explicitly selected in the select clause (optional)

Query Language¶

Keywords¶

select¶

Includes field values, taken from the search index, as an additional “selected_data” section in the search result items. You can select one or more fields to be attached to search results. The name of the field should consist the type prefix, otherwise the default text type will be used.

select text.field_name
select (text.first_field_name, text.second_field_name)

You can use fieldname aliasing, as known in SQL, for example:

1select text.field_1 as name, text.field_2 as author

You can use fieldname aliasing, as known in SQL, for example:

1select (text.field_1 as name, text.field_2 as author)

Note that parentheses are mandatory.

from¶

A list of entity aliases to search from. It can be one alias or group. Examples:

from one_alias
from (first_alias, second_alias)

where¶

The where clause defines the search parameters.

and, or¶

And and or operators are used to combine multiple clauses, allowing you to refine your search.

Syntax:

and field_type field_name operator value
or field_type field_name operator value

If the field type is not set, then text field type will be used.

offset¶

The offset clause allows to set the offset of the first result.

max_results¶

The set results count for the query.

order_by¶

The order_by clause allows to sort results in ascending or descending order.

Syntax:

1order_by field_type field_name direction

If the field type is set, the text field will be assigned. Direction - ASC, DESC. If the direction is not assigned, the ASC direction will be used.

Field Types¶

User should specify field type in query string. By default, if type is not set, it will be used text type. Supported field types: * text * integer * decimal * datetime

Operators¶

Different field types support different operators in the where block.

For String Fields¶

~ (CONTAINS) - operator ~ is used for set text field value. If search value is string, it must be quoted. Examples:
```
1name ~ value
2name ~ "string value"
```
!~ (NOT CONTAINS) - operator !~ is used for search strings without value. If the search value is a string, it must be quoted. Examples:
```
1name !~ value
2name !~ "string value"
```
like - operator like is used to finding records with a specified substring in any position (LIKE %value% statement behaviour). If the search value is a multi-word string that contains whitespaces, it should be enclosed in quotes. Examples:
```
1name like value
2name like "string value"
```
notlike - operator notlike is used to find records without a specified substring in any position (NOT LIKE %value% statement behaviour). If the search value is a multi-word string that contains whitespaces, it should be enclosed in quotes. Examples:
```
1name notlike value
2name notlike "string value"
```

For Numeric Fields¶

= (EQUALS) - operator = is used to search for records where the field value matches the specified value. Examples:
```
1integer count = 100
2decimal price = 12.5
3datetime create_date = "2013-01-01 00:00:00"
```
!= (NOT EQUALS) - operator != is used to search for records where the field value does not match the specified value. Examples:
```
1integer count != 5
2decimal price != 45
3datetime create_date != "2012-01-01 00:00:00"
```
>, <, <=, >= - Operators are used to search for the records where the field value is greater, less, less than or equals or greater than or equals of the specified value. Examples:
```
1integer count >= 5
2decimal price < 45
3datetime create_date > "2012-01-01 00:00:00"
```
in - operator in is used to search for the records where the value is in the specified set of data. Examples:
```
1integer count in (5, 10, 15, 20)
2decimal price in (12.2, 55.25)
```
!in - operator !in is used to search for records where the field value is not in the specified set of data. Examples:
```
1integer count !in (1, 3, 5)
2decimal price !in (2.1, 55, 45.4)
```

Query Brackets¶

User can combined operators in the search query with brackets.

Examples:

from oro_test where (owner ~ john and (integer count > 10 or float price = 10)) or (owner ~ mary and (integer count > 5 or float price = 150))

Query Examples¶

Search by demo products where the name contains the opportunity string and where the price is greater than 100.
```
1from demo_product where name ~ opportunity and double price > 100
```
Search and return entity data plus the name and the description of demo products.
```
1select (name, description) from demo_product
```
Search by all entities where the integer field count is not equal 10.
```
1integer count != 10
```
Search by all entities where text field all_text does not contain the opportunity string.
```
1all_text !~ "opportunity"
```
Select 10 results from the demo_products and demo_categories entities where the text field description contains test, order ASC by text field name and offset first result to 5.
```
from (demo_products, demo_categories) where description ~ test order_by name offset 5 max_results 10
```