SEARCH Operation

The SEARCH operation lets you search objects in the SmartOffice database by constructing SQL-like queries.

In your XML requests, use the <search> element to send search requests to XmlEngine.

The <search> element contains the following child elements, which are used to construct search requests:

Child Element Description
<object> This element specifies the object type to be searched (e.g., Contact) and the properties of that object type that will be returned in the response. If no properties are specified, the server returns a set of default properties.
<condition> This element contains your search conditions.
<sort> This element defines how search results should be sorted in the response.
These child elements are described in detail in the following sections.

Basic Search Example

The following example shows a basic search request that does the following:
  • Queries the database for all contacts whose last names start with "A."
  • Requests each matching contact's last name and first name.
  • Sorts the results by first name in ascending order.
 <request version='1.0'>
    <header>
        <office>myoffice</office>
        <user>jdoe</user>
        <password>password</password>
    </header>
    <search>
        <object>
            <Contact>
                <LastName/>
                <FirstName/>
            </Contact>
        </object>
        <condition>
            <expr prop='LastName' op='starts'>
                <v>A</v>
            </expr>
        </condition>
        <sort>
            <item prop='FirstName' order='asc'/>
        </sort>
    </search>
</request>

<object> Element

The <object> element specifies the object type to be searched. It also defines a template that specifies the object properties that will be returned in the XML response.

In the following example, the <object> element specifies that the search will be limited to Contact objects. For each record matching the query conditions, the response should contain the contact's first name, last name and preferred phone number.
 <object>
    <Contact>
        <LastName/>
        <FirstName/>
        <PreferredPhone>
            <PhoneNumber/>
        </PreferredPhone>
    </Contact>
</object>

<condition> Element

The <search> element must contain a <condition> child element that specifies the conditions of the search query. Conditions are defined in an XML structure that borrows from SQL database query syntax.

The <condition> element must contain at least one <expr> child element that defines the expression.

For example, the following <condition> element contains an expression (expr) that looks for Contact objects whose LastName property (prop) begins with "A." The expression employs an SQL-style LIKE operator (op) and A% wildcard pattern value (v).
<condition>
  <expr prop="Contact.LastName" op="like">
    <v>A%</v>
  </expr>
</condition>

Note the following regarding the <condition> element:
  • It can contain multiple <expr> elements.
  • It can contain nested <condition> child elements to create complex search queries.
  • It can contain <and> and <or> elements to define relationships between expressions and conditions.
To understand how multiple <condition> and <expr> elements can be used, refer to the following example. The main <condition> element contains a child <condition> element, which in turn contains its own <expr> elements. The resulting search request returns all contacts whose first names start with “A” and whose last names start with either “A” or “B.”
<request version='1.0'>
    <header>
        <office>myoffice</office>
        <user>jdoe</user>
        <password>password</password>
    </header>
    <search>
        <object>
            <Contact>
                <LastName/>
                <FirstName/>
            </Contact>
        </object>
        <condition>
            <expr prop='FirstName' op='starts'>
                <v>A</v>
            </expr>
            <and/>
            <condition>
                <expr prop='LastName' op='starts'>
                    <v>A</v>
                </expr>
                <or/>
                <expr prop='LastName' op='starts'>
                    <v>B</v>
                </expr>
            </condition>
        </condition>
    </search>
</request>

Expression

An “expr” node defines a minimal Boolean expression. The left side is always a property, but the right side can have one or more constant values or a property.

Left side information is defined by the attributes of the ‘expr” node.

Attributes of “expr”:
Name Description
prop

Left side property name. [object].[property].<sub-property>

Example:

Contact.Type

Contact.PreferredPhone.AreaCode

op operators
fn function name
The “v” node defines the right side information.

For example:
<condition>
  <expr prop="Contact.Name" op="starts">
    <v type="const">A</v>
  </expr>
</condition>

Attributes of “v”
Name Description
type Type of value. “prop” property name; “const” constant value. “ref” reference to current object’s property value. Default value is “const”. “ref” type normally used for “sync” method.
fn Function name.
The value type must be “const” for an operator: “between,” “in,” “like,” “starts,” “ends” and “contain.”

No “v” node should exist for the operator that is “null” and “not null.”

The “ref” type of value indicates that the value is a parameter and it comes from the specified property of the current object.

Operators

Operators include: eq, gt, ge, lt, le, not eq, between, not between, contain, not contain, like, not like, in, not in.
Operator Description
eq Equal, “=”
gt Greater then, “>”
ge Greater than or equal to, “>=”
lt Less then, “<”
le Less than or equal to, “<=”
ne Not equal, “<>”
between Between
like Like
contains Contains
starts Starts with
ends Ends with
in In
isnull Is null
not-between Not between
not-in Not in
not-null Is not null
not-like Not like
not-contains Not contains
not-starts Not starts
not-ends Not ends
any Any of the child object matches with given data. (Only supported by the sync operation’s logic_step condition.)
between

Between two values. It must be an integer type.

Sample:
<expr prop="Contact.Type" op="between">
  <v>1</v>
  <v>10</v>
</expr>

• like

The client is responsible for “escaping” the string value.

Sample:
<condition>
  <expr prop="Contact.Name" op="like">
    <v>A%</v>
  </expr>
</condition>

• contain

A means of testing if a string property contains a specified string. Special characters should not be used in a string value.

Sample:
<condition>
  <expr prop="Contact.Name" op="contain">
    <v>abc</v>
  </expr>
</condition>

The SQL query is “Name like ‘%abc%’.”

• starts

A means of testing if a string property starts with a specified string.

Sample:
<condition>
  <expr prop="Contact.Name" op="starts">
    <v>A</v>
  </expr>
</condition>

The SQL query is “Name like ‘A%’.”

• ends

A means of testing if a string property ends with a specified string.

Sample:
<condition>
  <expr prop="Contact.Name" op="ends">
    <v>A</v>
  </expr>
</condition>

The SQL query is “Name like ‘%A’.”

• in

A means of testing if the property is from a list of values. Only simple type values are allowed.

Sample:
<condition>
  <expr prop="Contact.Type" op="in">
    <v>1</v>
    <v>2</v>
    <v>3</v>
  </expr>
</condition>

The SQL query is “Type in (1,2,3)’.”

• Null

A means of testing if the property is null.

Sample:
<condition>
  <expr prop="Contact.Name" op="null"/>
</condition>

The SQL query is “Name is null.”

• not

“Not” can be used with: “eq”, “like”, “contain”, “starts”, “ends”, “in” and “null” operators.
<condition>
  <expr prop="Contact.Name" op="not-null"/>
</condition>

Functions

SmartIntegrator supports only the SQL functions that are also supported by the SmartOffice framework. The query object in the base system must support these functions. Functions can only be applied to the properties.
Function Name Description
YEAR Year
MONTH Month
DAY Day of month, 1 – 31
HOUR Hour
MINUTE Minute
SECOND Second
QUARTER Quarter of year
WEEKOFYEAR Week of year
DAYOFYEAR Day of year
DAYOFWEEK Day of week
UCASE Upper case
LCASE Lower case
Sample:

All contacts with a birthday in March:
<expr prop="Contact.Dob" op="eq" fn="MONTH">
  <v>3</v>
</expr>

Child Object Properties

Searchable properties of child objects can also be used in the condition. Relationships must be defined by the Sub-Type and foreign keys.

Sample: All contacts with a ZIP Code (Postal) of 91106.
<expr prop="Contact.PreferredAddress.Postal" op="eq">
  <v>91106</v>
</expr>

XML Response Attributes

Attribute Description
searchid Specifies the unique id of the search. The searchid will be necessary when the search results are on multiple pages. Please see pagination for more details.
pagesize Number of objects listed on each page. The default page size is 2000 records.
page If the results are greater than the pagesize, then this attribute will list the current page number to note where you are.
total Total number of records retrieved in the response.
more

Are there more records to be retrieved?

Specifies whether there are more records to be retrieved. This attribute can have the following values:

  • false: there are no more records to be retrieved.
  • true: there are more records to be retrieved. Please see xref (pagination) to learn how to retrieve these records.
Sample:

Example 1: Lists all contacts with a last name that starts with “R.”

Request XML
<request version='1.0'>
  <header>
    <office/>
    <user/>
    <password/>
  </header>
  <search>
    <object>
      <Contact>
        <LastName/>
        <FirstName/>
      </Contact>
    </object>
    <condition>
      <expr prop='LastName' op='starts'>
        <v>R</v>
      </expr>
    </condition>
  </search>
</request>

Response XML
<response version="1.0">
  <header>
    <sessionClosed/>
  </header>
  <search page="" more="false" pagesize="2000" total="1" searchid="">
    <Contact _type="obj" id="Contact.1.25">
      <LastName>Ryan</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
  </search>
  <_status>OK</_status>
</response>

Pagination

In the case of a large amount of data, the SmartIntegrator server provides the ability to produce paginated data returned within SEARCH results. Using the Search IDs generated after the first search, a client application can retrieve a selected element’s “searchid” attribute from the response XML.

To retrieve other pages, the “searchid” and requested “page” need to be specified in the request XML. Not specifying a “page” or leaving it blank defaults to retrieving the next page. Clients will not know the total number of records until the last page (more="false").

“keepsession” is also required.

Sample:

Request XML
<request version='1.0'>
  <header>
    <office/>
    <user/>
    <password/>
    <keepsession>true</keepsession>
  </header>
  <search pagesize="5">
    <object>
      <Contact>
        <LastName/>
        <FirstName/>
      </Contact>
    </object>
    <condition>
      <expr prop='LastName' op='starts'>
        <v>Ac</v>
      </expr>
    </condition>
  </search>
</request>

Response XML
<response version="1.0">
  <header/>
  <search page="0" more="true" pagesize="5" total="5" searchid="NQ==">
    <Contact _type="obj" id="Contact.1.21">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.22">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.23">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.24">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.26">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
  </search>
  <_status>OK</_status>
  <!-- Cost 21 mill seconds.-->
</response>

Request XML
<request version='1.0'>
  <header/>
  <search searchid="NQ==" pagesize="5">
    <object>
      <Contact>
        <LastName/>
        <FirstName/>
      </Contact>
    </object>
    <condition>
      <expr prop='LastName' op='starts'>
        <v>Ac</v>
      </expr>
    </condition>
  </search>
</request>

Response XML
<response version="1.0">
  <header/>
  <search page="-1" more="false" pagesize="5" total="5" searchid="NQ==">
    <Contact _type="obj" id="Contact.1.27">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.29">
      <LastName>Ackerman</LastName>
      <FirstName>Kathleen</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.31">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.34">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
    <Contact _type="obj" id="Contact.1.35">
      <LastName>Ackerman</LastName>
      <FirstName>Joseph</FirstName>
    </Contact>
  </search>
  <_status>OK</_status>
  <!-- Cost 10 mill seconds.-->
</response>

Why does the SEARCH request with pagination sometimes terminate before bringing back all of the records?

SEARCH requests are extremely powerful in letting you simultaneously retrieve data across many linked objects from the data model while dynamically filtering that data on criteria for more than one of the objects at a time. When used in conjunction with the pagination feature, the SEARCH request can push the SmartOffice server to its resource limits. This most often results in SmartOffice flushing the server-side cache for your SmartIntegrator session, which appears as a premature end to the paginated results being requested.

There is no easy way to advise development partners on exactly what might cause this issue as it is dependent on which objects are defined in the body and criteria of the SEARCH, how many records from those tables are visible to the user, and what level of stress the SmartOffice server is currently under.

We manage this by using a paginated SEARCH request with our full criteria to only retrieve the object id for the top-level object, then using a GET for each object id to get the data we need. The paginated SEARCH result is scalable up to hundreds of thousands of records for almost all objects when the SEARCH criteria is kept simple. In scenarios where the desired SEARCH criteria is complex, the criteria should be removed or simplified and the filtering needs to happen on the client-side.

Sample SEARCH Request to retrieve the object ids:
<request version='1.0'>
<header>
<office/>
<user/>
<password/>
<KeepSession/>
</header>
<search total="true" pagesize="2000">
<object>
<Policy/>
</object>
</search>
</request>

Sample SEARCH Response
<response version="1.0">
<header/>
<search searchid="MQ==" total="11694" pagesize="2000" more="true" page="0">
<Policy id="Policy.70746.15" _type="obj"/>
<Policy id="Policy.70746.16" _type="obj"/>
<Policy id="Policy.70746.17" _type="obj"/>...
<Policy id="Policy.70746.11005" _type="obj"/>
</search>
<_status>OK</_status>
</response>

Sample GET Request using the object ids and retrieving details
<request version='1.0'>
<header>
<office/>
<user/>
<password/>
<KeepSession/>
</header>
<get>
<Policy id="Policy.70746.15">
<UniqueID/>
<PolicyStatus/>
<PolicyNumber/>
<Premium/>
<AnnualPremium/>
<IssuedDate/>
<CreatedOn/>
<ModifiedOn/>
<Carrier>
<Vendor/>
</Carrier>
<PrimaryAdvisor>
<Agent>
<AgencyID/>
<CustomData>
<ObjectIDVal/>
<AlphaNum10/>
</CustomData>
</Agent>
</PrimaryAdvisor>
<PendingCase>
<CashReceived/>
</PendingCase>
</Policy>...
<Policy id="Policy.70746.11005">
<UniqueID/>
<PolicyStatus/>
<PolicyNumber/>
<Premium/>
<AnnualPremium/>
<IssuedDate/>
<CreatedOn/>
<ModifiedOn/>
<Carrier>
<Vendor/>
</Carrier>
<PrimaryAdvisor>
<Agent>
<AgencyID/>
<CustomData>
<ObjectIDVal/>
<AlphaNum10/>
</CustomData>
</Agent>
</PrimaryAdvisor>
<PendingCase>
<CashReceived/>
</PendingCase>
</Policy>
</get>
</request>

Sort

The Sort option defines the sorting information. Only searchable properties can be sorted.
<sort>
  <item prop="AreaCode" order="asc"/>
</sort>

The attribute “prop” is the name of a property but it is a short name and does not include an object name.

The attribute “order” can have only one of the two values: “asc” or “desc.” The default is “asc.”



Topic revision: 26 Aug 2022, DinosLambropoulos
 

This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback