Skip to content

Customize Query Options with Data Query Builder

The DataQueryBuilder is an API for building OData Query Options.

  • Unless specified, the DataQueryBuilder API methods return the DataQueryBuilder instance which provides an easy mechanism to chain multiple API invocations.

  • Unless specified, the DataQueryBuilder API methods implement a variadic argument interface where multiple arguments are specified using comma-separated strings.

For complete list of APIs for DataQueryBuilder, please see DataQueryBuilder API Reference

Top-Level System Query Options

Expand

/**
 * @param {string[]} expandOptions - variadic arguments specified as comma-separated strings
 * @return {DataQueryBuilder}
 */
public expand(...expandOptions: string[]): this;

The OData expand options are added to the query by invoking the DataQueryBuilder expand API. This variadic method accepts a comma-separated list of strings representing the expand system options.

// Example
dataQueryBuilder.expand('EntitySet1', 'EntitySet2');

Filter

There are two API methods provided to create OData filter statements, both methods return a FilterBuilder instance to the caller.

Setting the Root Filter Term

type FilterTerm = (FilterBuilder | string);

/**
 * @param {FilterTerm[]} terms - variadic arguments specified as comma-separated FilterTerms
 * @return {FilterBuilder}
 */
public filter(...terms: FilterTerm[]): FilterBuilder;

This API method creates a FilterBuilder and adds it to the current DataQueryBuilder instance.

Notes:

  • The parameters to this API can be either a FilterBuilder or a string. This combinations is known as a FilterTerm. A filter can be thought of as a tree of Filter Terms grouped together using the FilterBuilder APIs.

  • Using this method breaks DataQueryBuilder instance method chaining.

dataQueryBuilder.filter().expand(); // invalid calling sequence

Getting an Independent Filter Term

/**
 * @param {FilterTerm[]} terms - variadic arguments specified as comma-separated FilterTerms
 * @return {FilterBuilder}
 */
public filterTerm(...terms: FilterTerm[]): FilterBuilder;

This API method creates a FilterBuilder without adding it to the current DataQueryBuilder instance. Use the API to construct a filter statement isolated from the DataQueryBuilder and add once the filter terms are complete using the FilterBuilder APIs.

Getting the Built-In Search Filter Term

/**
 * @param {string} searchString - the string you want MDK to search the bound properties with
 * @return {FilterBuilder}
 */
public mdkSearch(searchString: string): FilterBuilder;

This API method created a FilterBuilder that will contain the Filter Term to execute the automatic search for a given string. That is the properties that are directly bound in the table or using dynamic target paths will be searched on by the built-in search functionality.

The filter builder will not be added to the current DataQueryBuilder instance. The caller is responsible for that in order to place it in the right position with the other search terms.

Grouping

Grouping of Filter Terms is fundamental to the construction of filter strings. This allows for the right precedence to be specified for the filter string. The DataQueryBuilder supports two options for grouping: explicit and implicit.

Explicit Grouping

This is specified when the term is a string containing the OData grouping operators right in the string.

// Note that string specifies how the terms are grouped
dataQueryBuilder.filter("(Priority eq '1' or Priority eq '3')");

// Results in the following query
$filter=(Priority eq '1' or Priority eq '3')
Implicit Grouping

Implicit grouping is applied when the two or more arguments are passed to a variadic method.

// Note that variadic arguments are specifying how the terms are grouped
dataQueryBuilder.filter().or("Priority eq '1'", "Priority eq '3'");

// Results in the following query (note the parentheses are added automatically)
$filter=(Priority eq '1' or Priority eq '3')

Here is an example mixing both methods and creating a more complex query tree with the right precedence:

const term1 = "(OrderType eq 'PM02' or OrderType eq 'PM03')"; // explicit

const term2 = dataQueryBuilder.filterTerm().equal('Priority', '2');
const term3 = dataQueryBuilder.filterTerm().equal('Priority', '3');
const term2or3 = dataQueryBuilder.filterTerm().or(term2, term3); // implicit

dataQueryBuilder.filter(term1).and(term2or3); // combined

// Results in the following query
$filter=(OrderType eq 'PM02' or OrderType eq 'PM03') and (Priority eq 2 or Priority eq 3)
Using ES6 Spread Operator

In cases where an Array is used to construct a list of 2 or more arguments the Spread operator must be applied prior to invoking the DataQueryBuilder method.

const searchString = context.searchString;
// This is an example for when arguments must be calculated
const prioritySearch = [];
if (searchString.toLowerCase().includes('high')) {
  prioritySearch.push(`Priority eq '1'`);
}
if (searchString.toLowerCase().includes('medium')) {
  prioritySearch.push(`Priority eq '2'`);
}
if (searchString.toLowerCase().includes('low')) {
  prioritySearch.push(`Priority eq '3'`);
}

// The spread operator is applied to convert the array to a variadic list of arguments
if (prioritySearch.length > 1) {
  dataQueryBuilder.filterTerm().or(...prioritySearch);
} else if (prioritySearch.length > 0) {
  dataQueryBuilder.filterTerm(prioritySearch[0]);
}

Order By

The OData orderby options are added to the query by invoking the DataQueryBuilder orderBy API. This variadic method accepts a comma-separated list of strings representing the orderby system options. In addition, each orderby option can embed a direction, asc or desc.

/**
 * @param {string[]} orderByOptions - variadic arguments specified as comma-separated strings
 * @return {DataQueryBuilder}
 */
public orderBy(...orderByOptions: string[]): this;

// Example
dataQueryBuilder.orderBy('Property1');

// For this query order by Property2 in descending order
dataQueryBuilder.orderBy('Property2 desc');

Select

The OData select options are added to the query by invoking the DataQueryBuilder select API. This variadic method accepts a comma-separated list of strings representing the select system options.

/**
 * @param {string[]} selectOptions - variadic arguments specified as comma-separated strings
 * @return {DataQueryBuilder}
 */
public select(...selectOptions: string[]): this;

// Example
dataQueryBuilder.select('Property1', 'Property2');

Top

The OData top option is specified by invoking the DataQueryBuilder top API. This non variadic method accepts a single string or number representing the top system option.

/**
 * @param {number or string} top
 * @return {DataQueryBuilder}
 */
public top(top: number | string): this;

// Example
dataQueryBuilder.top(5);

Skip

The OData skip option is specified by invoking the DataQueryBuilder skip API. This non variadic method accepts a single string or number representing the skip system option.

/**
 * @param {number or string} skip
 * @return {DataQueryBuilder}
 */
public skip(skip: number | string): this;

// Example
dataQueryBuilder.skip(2);

Chaining

The DataQueryBuilder API supports method chaining by returning the instance of the DataQueryBuilder for each method invocation. DataQueryBuilder method chaining breaks once the filter API method is invoked and an instance of FilterBuilder returned to the caller.

// Example
dataQueryBuilder.top(50).skip(50);

Accessors

The DataQueryBuilder also provides APIs to determine the state of the DataQueryBuilder instance. e.g.

  • hasOrderBy to check if the DataQueryBuilder currently has order by option set
  • orderByOption to get the order by options that is currently set.

For all the list of accessor APIs, please see DataQueryBuilder API Reference

Debugging

Debug String

/**
 * @return {string}
 */
 get debugString(): string;

This API returns a string representation of the current builder state without doing any building.

// Example
console.log(dataQueryBuilder.debugString);
- EXPAND:  Operations, Equipment
- ORDERBY: OrderId
- SELECT:  -
- TOP:     -
- SKIP:    -
- FILTER:  (OrderType eq 'PM02' and (Priority eq '3' or MDKSearch("Low")))

Branded Settings OData Tracing

By enabling this, one can see the exact queries being sent to the OData Provider to verify the DataQueryBuilder is behaving as expected.

{
  "DebugSettings": {
    "TracingEnabled": true,
    "Categories": [
      "mdk.trace.odata"
    ]
  }
}

Putting It All Together

Here we have an example of a page that displays an Object Table with custom searching:

{
  "Caption": "Custom Search",
  "Controls": [
    {
      "Sections": [
        {
          "Header": {
            "UseTopPadding": false
          },
          "ObjectCell": {
            "AccessoryType": "disclosureIndicator",
            "Description": "{OrderDescription}",
            "DetailImage": "/MDKSectionsApp/Images/workorder.png",
            "DetailImageIsCircular": true,
            "Icons": [
              "/MDKSectionsApp/Images/icon_severity_medium.png",
              "/MDKSectionsApp/Images/open.png"
            ],
            "StatusText": "/MDKSectionsApp/Rules/ObjectTable/PriorityDisplayText.js",
            "SubstatusText": "/MDKSectionsApp/Rules/ObjectTable/LastChangeTimeDisplayText.js",
            "Title": "{OrderId}"
          },
          "Search": {
            "BarcodeScanner": true,
            "Delay": 500,
            "Enabled": true,
            "MinimumCharacterThreshold": 3,
            "Placeholder": "Item Search"
          },
          "Target": {
            "EntitySet": "MyWorkOrderHeaders",
            "QueryOptions": "/MDKSectionsApp/Rules/ObjectTable/CustomSearchQueryOptions.js",
            "Service": "/MDKSectionsApp/Services/Amw.service"
          },
          "Visible": true,
          "_Type": "Section.Type.ObjectTable"
        }
      ],
      "_Name": "SectionedTable",
      "_Type": "Control.Type.SectionedTable"
    }
  ],
  "_Name": "CustomSearchPage",
  "_Type": "Page"
}

Here are the format rules being used for the Status and Substatus field values of the Object Cells:

export default function PriorityDisplayText(context) {
  switch (context.binding.Priority) {
    case '1':
      return 'High';
    case '2':
      return 'Medium';
    case '3':
      return 'Low';
    default:
      return '';
  }
}
export default function LastChangeTimeDisplayText(context) {
  return context.formatDate(context.binding.LastChangeTime);
}

We have the following requirements:

  • The table must be sorted by OrderId.
  • The table must always only show orders with OrderTyp equal to PM02.
  • The user must be able to search on the value shown in the Description field of the Object Cell which is bound to the OrderDescription property.
  • The user must be able to search on the StatusText field of the Object Cell which is displaying a string for different Priority property values.
    • If the user searches for >1 priorities at the same time (i.e. "Low Medium") orders of both priorities must be displayed
  • The user must be able to search by the year shown in the SubstatusText which is displaying a string based on the LastChangeTime property when they are searching for a number in the format "NNNN".
export default function CustomSearchQueryOptions(context) {

  // context in this example is a SectionedTableProxy
  const searchString = context.searchString;

  let qob = context.dataQueryBuilder();

  qob.expand('Operations', 'Equipment').orderBy('OrderId');

  // Setup whatever search-independent filtering the app needs to do all the time
  let appFilter = "OrderType eq 'PM02'";

  if (!searchString) {
    // no other calculations are needed, can return right away
    qob.filter(appFilter);
    return qob;

    // Could have also returned this here for backwards
    // compatibility, and it would have worked the same
    return "$filter=OrderType eq 'PM02'&$expand=Operations,Equipment&$orderby=OrderId";
  }

  // User can search for "Medium" to return all Priority 2 orders
  // or for "High Medium" to return all Priority 1 and 2 orders, etc...
  let prioritySearchTerms = [];
  if (searchString.toLowerCase().includes('high')) {
    prioritySearchTerms.push(`Priority eq '1'`);
  }
  if (searchString.toLowerCase().includes('medium')) {
    prioritySearchTerms.push(`Priority eq '2'`);
  }
  if (searchString.toLowerCase().includes('low')) {
    prioritySearchTerms.push(`Priority eq '3'`);
  }
  let prioritySearch = undefined;
  if (prioritySearchTerms.length > 0) {
    prioritySearch = qob.filterTerm(`(${prioritySearchTerms.join(` or `)})`);
  }

  // User can search for orders in a specific year if they search
  // for a number of four digits
  var yearSearch = undefined;
  if (!isNaN(searchString)) {
    var searchNumber = +searchString;
    if (searchNumber >= 1000 && searchNumber <= 9999) {
      yearSearch = qob.filterTerm('year(LastChangeTime) eq ' + searchNumber);
    }
  }

  // User can search using the standard automatic MDK search
  // on the Description field, so add that in the query
  var search = undefined;
  if (prioritySearch && yearSearch) {
    search = qob.filterTerm().or(prioritySearch, yearSearch, qob.mdkSearch(searchString));
  } else if (prioritySearch) {
    search = qob.filterTerm().or(prioritySearch, qob.mdkSearch(searchString));
  } else if (yearSearch) {
    search = qob.filterTerm().or(yearSearch, qob.mdkSearch(searchString));
  } else {
    search = qob.mdkSearch(searchString);
  }

  // Put the terms together with the right precedence
  qob.filter().and(appFilter, search);

  // console.log(qob.debugString);

  return qob;
}

Last update: August 12, 2020