Search

Search

Version 23.0.9145

Search

CData API Server supports the OData $search functionality, as shown in the following example:

http://MyServer:MyPort/api.rsc/Cars?$search='Peugeot'

Note: In addition to standard search, MySQL, SQL Server, and MongoDB also support full-text searches.

Standard $search is the simplest way to retrieve records that match certain strings. This method uses SQL queries with the LIKE operator to return results. Standard search is supported by all CData data providers.

Enabling Standard Search for Specific Columns

By default, search is enabled for all string columns. If necessary, you can restrict searching to specific columns. In the schema for a resource, add other:search="true" to each column definition you want to include in searches. Other string columns are then assumed to be nonsearchable.

<api:info title="Cars" desc="Access the Cars database through REST APIs." connection="SQLiteCars">
  <attr name="ID" key="true" xs:type="int"  />
  <attr name="Make"         xs:type="string"   other:search="true" />
  <attr name="Model"        xs:type="string"   other:search="true" />
  <attr name="Trim"         xs:type="string"    />
  <attr name="Color"        xs:type="string"    />
  <attr name="Cost"         xs:type="double"    />
  <attr name="CreatedDate"  xs:type="datetime"  />
  <attr name="InStock"      xs:type="boolean"   />
</api:info>

Consider this request:

http://MyServer:MyPort/api.rsc/Cars?$search='Peugeot'

This request results in the following SQL query being passed to the database provider:

SELECT Make, Model, Trim, Color FROM Cars WHERE (Make LIKE "%Peugeot%") OR (Model LIKE "%Peugeot%")

To search for an exact expression that includes spaces, enclose the expression with double quotes. The following expression searches for records that match the phrase Mini Cooper:

http://MyServer:MyPort/api.rsc/Cars?$search="Mini Cooper"

Using Logical Operators

API Server $search functionality supports the following operators:

Operator Description Sample OData
NOT Returns records that do not match the term api.rsc/Cars?$search=NOT Peugeot
AND Returns records that match both terms api.rsc/Cars?$search=Peugeot AND Renault
OR Returns records that match either term api.rsc/Cars?$search=Peugeot OR Renault

You can combine and order logical operators with multiple search expressions using parentheses.

Note: In accordance with OData standards, specifying two search terms separated by a space and not surrounded by quotes is equivalent to using the AND operator.

Full-Text Search

Full-text search is a more powerful way to find records that match a string value. This method uses indexes to find relevant results more efficiently in a large search pool. You must enable full-text search on the database side: to do so, refer to the documentation for your database. Full-text search is supported in SQL Server, MySQL, and MongoDB.

Note: The configuration of the full-text search might affect what results are returned.

Enabling Full-Text Search

To enable full-text search in a resource, check the box in the Resource Editor when importing it to API Server. Alternatively, add the following to the title line in the resource schema: other:fulltextsearch="true", as shown in the following example.

<api:info title="Cars" desc="Access the Cars database through REST APIs." connection="MySQLCars" other:fulltextsearch="true" >
  <attr name="ID" key="true" xs:type="int"  />
  <attr name="Make"         xs:type="string"   />
  <attr name="Model"        xs:type="string"   />
  <attr name="Trim"         xs:type="string"   />
  <attr name="Color"        xs:type="string"   />
  <attr name="Cost"         xs:type="double"   />
  <attr name="CreatedDate"  xs:type="datetime" />
  <attr name="InStock"      xs:type="boolean"  />
</api:info>

Using Full-Text Search

After you have enabled full-text search on a resource, calling the search from OData is done in the same way as the standard search:

http://MyServer:MyPort/api.rsc/Cars?$search='Volkswagen'

The previous request results in the following SQL query being passed to the database provider:

SELECT Make, Model, Trim, Color FROM Cars WHERE SEARCH('Make,Model,Trim,Color', 'Volkswagen') IS TRUE

Specifying Search Columns

By default, API Server builds a query using all of the string columns in the table. You can restrict which fields to search by setting individual columns as searchable in their column definition. However, in some databases, such as in MongoDB, the search columns are predefined server-side. In this case, manually specifying search columns might not affect the results returned from the search.

Specifying Multiple Search Terms

In full-text search, the search parameter is passed in its entirety to the search query. Logical operators, such as AND and OR are handled by the underlying data source. To search for terms that include a space, be sure to use double-quotes.

For example, the OData call:

http://MyServer:MyPort/api.rsc/Cars?$search="Golf GTI" OR A4

results in the following query being passed to the database provider:

SELECT Make, Model, Trim, Color FROM Cars WHERE SEARCH('Make,Model,Trim,Color', '"Golf GTI" or A4') IS TRUE