Customize Query Options with Data Query Builder¶
The DataQueryBuilder
is an API for building OData Query Options.
-
Unless specified, the
DataQueryBuilder
API methods return theDataQueryBuilder
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 astring
. This combinations is known as aFilterTerm
. A filter can be thought of as a tree of Filter Terms grouped together using theFilterBuilder
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 theDataQueryBuilder
currently has order by option setorderByOption
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 toPM02
. - The user must be able to search on the value shown in the
Description
field of the Object Cell which is bound to theOrderDescription
property. - The user must be able to search on the
StatusText
field of the Object Cell which is displaying a string for differentPriority
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 theLastChangeTime
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;
}