In this section, you will see in detail about how to build query using Query
class and consume
the data source.
from
The from
method is used to specify the resource name or table name from where the data should be retrieved.
import { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().take(8))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
The select
method is used to select particular fields or columns from the data source.
import { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().select(['OrderID', 'CustomerID', 'EmployeeID']).take(8))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
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 { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query()
.expand('Employee').select(['OrderID', 'CustomerID', 'Employee.FirstName']).take(8))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
interface IOrders {OrderID: number, CustomerID: string, Employee: {FirstName: string} };
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.Employee.FirstName}</td>
</tr>)
}
}
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 { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().sortBy('CustomerID', 'descending').take(8))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
Multi sorting can be performed by simply chaining the multiple
sortBy
methods.
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 { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().where('EmployeeID', 'equal', 3).take(8))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
Filter operators are generally used to specify the filter type. The various filter operators
supported by DataManager
is listed below.
These filter operators are used for creating filter query using
where
method andPredicate
class.
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 { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Predicate, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
let predicate: Predicate = new Predicate('EmployeeID', 'equal', 3);
predicate = predicate.or('EmployeeID', 'equal', 2);
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().where(predicate).take(8))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
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 { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Predicate, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
let predicate: Predicate = new Predicate('EmployeeID', 'equal', 3);
predicate = predicate.or('EmployeeID', 'equal', 2);
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().search('VI', ['CustomerID']))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
You can search particular fields by passing the field name collection in the second argument of
search
method.
DataManager
allow you to group records by category. The group
method is used to add group query.
import { getValue } from '@syncfusion/ej2-base';
import { DataManager, Group, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { GroupRow } from './groupTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().group('CustomerID').take(8))
.then((e: ReturnOption) => {
const res = (e.result as Group[]).map((row: Group) => (<GroupRow {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
{ getValue('items', this.state) }
</table>)
}
}
import { getValue } from '@syncfusion/ej2-base';
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
if (getValue('caption', item)) {
return (<tr>
<td colSpan={3}>{getValue('caption', item)}</td>
</tr>)
}
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
import { Group } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
export class GroupRow extends React.Component<{}, {}>{
public getRows(data: object[]) {
return data.map((row: IOrders) => (<Row {...row}/>));
}
public render() {
const item: Group = this.props as Group;
const ag: { caption: string } = { caption: item.field + ' - '
+ (item.items && item.items[0][item.field as string]) };
return (<tbody> <Row {...ag}/> { this.getRows(item.items as object[]) }</tbody>)
}
}
export interface IOrders {
SNO: number,
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
Multiple grouping can be done by simply chaining the
group
method.
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 { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Predicate, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [] };
let predicate: Predicate = new Predicate('EmployeeID', 'equal', 3);
predicate = predicate.or('EmployeeID', 'equal', 2);
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().page(2, 8))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{ getValue('items', this.state) }</tbody>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
The aggregate
method allows you to get aggregated value for a field based on the type.
The built-in aggregate types are,
import { getValue } from '@syncfusion/ej2-base';
import { Aggregates, DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { AggregateRow } from './aggregateTemplate';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'https://js.syncfusion.com/demos/ejServices/Wcf/Northwind.svc/Orders';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [], aggregates: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().take(5).requiresCount().aggregate('min', 'EmployeeID'))
.then((e: ReturnOption) => {
const agg: { min: object } = { min: (e.aggregates as Aggregates)['EmployeeID - min'] };
const ret : React.ReactElement = <AggregateRow {...agg}/>;
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
aggregates: [ret],
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
<tbody>{getValue('items', this.state)}</tbody>
<tfoot>{getValue('aggregates', this.state)}</tfoot>
</table>)
}
}
import * as React from 'react';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public render() {
const item: IOrders = this.props as IOrders;
return (<tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>)
}
}
import { Aggregates } from '@syncfusion/ej2-data';
import * as React from 'react';
export class AggregateRow extends React.Component<{}, {}>{
public render() {
const item: Aggregates = this.props as Aggregates;
return (<tr>
<td/>
<td/>
<td>Min: {item.min}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}
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 { getValue } from '@syncfusion/ej2-base';
import { DataManager, ODataAdaptor, Query, ReturnOption } from '@syncfusion/ej2-data';
import * as React from 'react';
import { IOrders } from './orders';
import { Row } from './rowTemplate';
const SERVICE_URI: string = 'http://mvc.syncfusion.com/Services/Northwnd.svc/';
export default class App extends React.Component<{}, {}>{
constructor(props: object) {
super(props);
this.state = { items: [], aggregates: [] };
new DataManager({ url: SERVICE_URI, adaptor: new ODataAdaptor })
.executeQuery(new Query().from('Orders').take(3).hierarchy(
new Query()
.foreignKey("OrderID")
.from("Order_Details")
.sortBy("Quantity"),
() => [10248, 10249, 10250] // Selective loading of child elements
))
.then((e: ReturnOption) => {
const res = (e.result as IOrders[]).map((row: IOrders) => (<Row {...row}/>));
this.setState({
items: res
});
});
}
public render() {
return (<table id='datatable' className='e-table'>
<thead>
<tr><th>Order ID</th><th>Customer ID</th><th>Employee ID</th></tr>
</thead>
{getValue('items', this.state)}
</table>)
}
}
import * as React from 'react';
import { ChildRow } from './childTemplate';
import { IOrders } from './orders';
export class Row extends React.Component<{}, {}>{
public getRows(data: object[]) {
const dat: object[]=data.map((row: object) => (<ChildRow {...row}/>));
return dat;
}
public render() {
const item: IOrders = this.props as IOrders;
return (<tbody><tr>
<td>{item.OrderID}</td>
<td>{item.CustomerID}</td>
<td>{item.EmployeeID}</td>
</tr>
<tr><td colSpan={3}>
<table id='datatable' className='e-table'>
<thead><tr><th>ID</th><th>Price</th><th>Quantity</th></tr></thead>
<tbody>{ this.getRows(item.Order_Details)}</tbody>
</table>
</td></tr>
</tbody>)
}
}
import * as React from 'react';
interface IProducts { ProductID: number; UnitPrice: number; Quantity: number; }
export class ChildRow extends React.Component<{}, {}>{
public render() {
const item: IProducts = this.props as IProducts;
return (<tr>
<td>{item.ProductID}</td>
<td>{item.UnitPrice}</td>
<td>{item.Quantity}</td>
</tr>)
}
}
export interface IOrders {
OrderID: number;
EmployeeID: number;
CustomerID: string;
Order_Details: object[];
}