The CData API Server supports the OData $search functionality. In addition to standard search, MySQL, SQL Server, and MongoDB support full-text search functionality.

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

Standard Search

Standard search, or just Search is the simplest method of retrieving records that match a certain strings. This search method uses SQL queries with the LIKE operator to return results. Standard Search, is supported with all CData data providers.

Enabling Standard Search for Specific Columns

Search is enabled by default for all string columns. It can be restricted to specific columns if necessary. In the schema for a resource, add other:search="true" for each column definition to include the column in searches. Other string columns will be 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>

Using Standard Search

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 will search for records that match the exact phrase “Mini Cooper”:

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

Using Logical Operators

The $search functionality of the CData API Server supports the following operators:

Logical operators with multiple search expressions can be combined and ordered using parentheses. Note that 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 method for finding records matching a string value. This method uses indexes to find relevant results more efficiently in a large search pool. Full-text search must be enabled on the database side; to do so, please refer to the documentation for your database. Note that the configuration of the full-text search may affect what results are returned. Full-text search is currently supported in SQL Server, MySQL, and MongoDB.

Enabling Full-Text Search

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

<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

Calling the search from OData is done similarly as the standard search:

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

This 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, the CData API Server will build a query using all the string columns in the table. You can restrict which fields to search on by setting each individual columns as searchable in their column definition. Note that in some databases, such as in MongoDB, the search columns are predefined server-side. In this case, manually specifying search columns may 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, make sure to use double-quotes.

For example, the OData call:

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

will result 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