Querying in EJ2 TypeScript Data control
2 Dec 202324 minutes to read
In this section, you will see in detail about how to build query using Query class and consume the data source.
Specifying resource name using from
The from method is used to specify the resource name or table name from where the data should be retrieved.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().from('Orders').take(8)).then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Projection using select
The select method is used to select particular fields or columns from the data source.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().select(['OrderID', 'CustomerID', 'EmployeeID']).take(8))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Eager loading navigation properties
You can use the expand method to eagerly load navigation properties. The navigation properties values are accessed using appropriate field names separated by dot(.) sign.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${Employee.FirstName}</td></tr>';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
table.innerHTML = '<tr><th>OrderID</th><th>CustomerID</th><th>Employee Name</th></tr>';
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().expand('Employee').select(['OrderID', 'CustomerID', 'Employee.FirstName']).take(8))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Sorting
You can use the sortBy method to perform sort operation in the data source. Default sorting order is ascending
. To change the sort order, either you can specify the second argument of sortBy as descending
or use the sortByDesc method.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().sortBy('CustomerID', 'descending').take(8))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Multi sorting can be performed by simply chaining the multiple
sortBy
methods.
Filtering
You can use the where method to build filter criteria which allows you to get reduced view of records. The where method can also be chained to form multiple filter criteria.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().where('EmployeeID', 'equal', 3).take(8))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Filter Operators
Filter operators are generally used to specify the filter type. The various filter operators supported by DataManager is listed below.
- greaterthan
- greaterthanorequal
- lessthan
- lessthanorequal
- equal
- notequal
- startswith
- endswith
- contains
These filter operators are used for creating filter query using where method and Predicate class.
Build complex filter criteria using Predicate
Sometimes chaining where method is not sufficient to create very complex filter criteria, in such cases we can use Predicate class to create composite filter criteria.
import { DataManager, Query, ODataV4Adaptor, Predicate, ReturnOption } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
//Building complex filter criteria using `Predicate`
let predicate: Predicate = new Predicate('EmployeeID', 'equal', 3);
predicate = predicate.or('EmployeeID', 'equal', 2);
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().where(predicate).take(8))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Searching
You can use the search method to create search criteria, it differs from the filter in the way that search criteria will applied to all fields in the data source whereas filter criteria will be applied to a particular field.
import { DataManager, Query, ReturnOption } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
import { data } from './datasource.ts';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager(data)
.executeQuery(new Query().search('VI', ['CustomerID']))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='loader'>Loading....</div>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
You can search particular fields by passing the field name collection in the second argument of search method.
Grouping
DataManager allow you to group records by category. The group method is used to add group query.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${field} - ${key}</td><td></td><td></td></tr>${for(item of items)}<tr><td>${item.OrderID}</td><td>${item.CustomerID}</td><td>${item.EmployeeID}</td></tr>${/for}';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().group('CustomerID').take(8))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Multiple grouping can be done by simply chaining the group method.
Paging
You can query paged data using page method. This allow you to query particular set of records based on the page size and index.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().page(2, 8))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Aggregation
The aggregate method allows you to get aggregated value for a field based on the type. The built-in aggregate types are,
- sum
- average
- min
- max
- count
- truecount
- falsecount
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>';
let compiledFunction: Function = compile(template);
let footerFn: Function = compile('<tr><td></td><td></td><td>Minimum: ${min}</td></tr>');
const SERVICE_URI: string = 'https://services.odata.org/V4/Northwind/Northwind.svc/Orders/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().take(5).requiresCount().aggregate('min', 'EmployeeID'))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
});
table.appendChild(footerFn({ min: e.aggregates['EmployeeID - min'] })[0]);
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>
Hierarchical query
You can use the hierarchy method to build nested query. The hierarchical queries are commonly required when you use foreign key binding.
The foreignKey method is used to specify the key field of the foreign table and the second argument of the hierarchy method accepts a selector function which selects the records from the foreign table.
import { DataManager, Query, ReturnOption, ODataV4Adaptor } from '@syncfusion/ej2-data';
import { compile } from '@syncfusion/ej2-base';
let template: string = '<tr><td>${OrderID}</td><td>${CustomerID}</td><td>${EmployeeID}</td></tr>'
let group: string = '<tr><td colspan=3>' +
'<table id="datatable" class="e-table"><tr><th>ID</th><th>Price</th><th>Quantity</th></tr>' +
'${for(detail of Order_Details)}<tr><td>${detail.ProductID}</td><td>${detail.UnitPrice}</td><td>${detail.Quantity}</td></tr>${/for}' +
'<table></td></tr>';
let compiledFunction: Function = compile(template);
let groupFn = compile(group);
const SERVICE_URI = 'https://services.odata.org/V4/Northwind/Northwind.svc/';
let table: HTMLElement = (<HTMLElement>document.getElementById('datatable'));
new DataManager({ url: SERVICE_URI, adaptor: new ODataV4Adaptor })
.executeQuery(new Query().from('Orders').take(3).hierarchy(
new Query()
.foreignKey("OrderID")
.from("Order_Details")
.sortBy("Quantity"),
function () {
// Selective loading of child elements
return [10248, 10249, 10250]
}
))
.then((e: ReturnOption) => {
(<Object[]>e.result).forEach((data: Object) => {
table.appendChild(compiledFunction(data)[0]);
table.appendChild(groupFn(data)[0]);
});
});
<!DOCTYPE html>
<html lang="en">
<head>
<title>EJ2 Grid</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta name="description" content="Typescript Grid Control" />
<meta name="author" content="Syncfusion" />
<link href="index.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-calendars/styles/material.css" rel="stylesheet" />
<link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/systemjs/0.19.38/system.js"></script>
<script src="systemjs.config.js"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>
<body>
<div id='loader'>Loading....</div>
<div id='container'>
<div id='Grid'></div>
<table id='datatable' class='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</body>
</html>