Filtering
9 Jan 201824 minutes to read
Filtering is a basic technique in DataManager query. The “where” query is used to filter some particular or related records from the data source to review details of records.
Filter Operators
Filter operators are generally used to specify the filter type. The various filter operators corresponding to the type of the column is listed in the following table.
| Column type | Filter operators |
|---|---|
| Number |
ej.FilterOperators.greaterThan ej.FilterOperators.greaterThanOrEqual ej.FilterOperators.lessThan ej.FilterOperators.lessThanOrEqual ej.FilterOperators.equal |
| String |
ej.FilterOperators.startsWith ej.FilterOperators.endsWith ej.FilterOperators.contains ej.FilterOperators.equal ej.FilterOperators.notEqual |
| Boolean |
ej.FilterOperators.equal ej.FilterOperators.notEqual |
| Date |
ej.FilterOperators.greaterThan ej.FilterOperators.greaterThanOrEqual ej.FilterOperators.lessThan ej.FilterOperators.lessThanOrEqual ej.FilterOperators.equal |
lessThan
This operator is used to get the records with values less than that of the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,10).where("OrderID", "lessThan", 10252, false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
greaterThan
This operator is used to get the records with values greater than that of the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5)
.where("OrderID", "greaterThan", 10263, false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
lessThanOrEqual
This operator is used to get the records with values less than or equal to the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders")
.select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,10)
.where("OrderID", "lessThanOrEqual", 10251, false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
greaterThanOrEqual
This operator is used to get the records with values greater than or equal to the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5)
.where("OrderID", "greaterThanOrEqual", 10256, false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
equal
This operator is used to get the records with values equal to that of the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5).where("EmployeeID", "equal", 6, false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
notEqual
This operator is used to get the records with values not equal to that of the filter value specified.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
var dataManager = ej.DataManager({//oData Adaptor with DataManager
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders")
.select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5)
.where("ShipCountry", "notEqual", "Rio de Janeiro", false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
contains
This operator is used to get the records that contains the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders")
.select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5)
.where("CustomerID", "contains", "A", false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
startswith
This operator is used to get the records that starts with the filter value specified.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5).where("CustomerID", "startswith", "V", false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
endswith
This operator is used to get the records that ends with the filter value specified.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5).where("CustomerID", "endswith", "E", false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
and predicate
The and predicate is used to add n-number of predicates with “and” condition and filter the data.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders")
.select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5)
.where(ej.Predicate("OrderID", ej.FilterOperators.greaterThan, 10399, true).and("CustomerID", ej.FilterOperators.startsWith, "V", true));
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
or predicate
Using this method you can add n-number of predicates with or condition and filter the data.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
//oData Adaptor with DataManager
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders")
.select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5)
.where(ej.Predicate("EmployeeID", ej.FilterOperators.greaterThan, 6, true)**.or**("CustomerID", ej.FilterOperators.startsWith, "S", true));
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>Result of the above code example is illustrated as follows.
Containment Operators
notcontains
This operator is used to get the records that not contains the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1,5).where("ShipCountry", "notcontains", "CAN", false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>
contains
This operator is used to get the records that contains the filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1, 5).where("ShipCountry", "contains", "CAN", false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>
in
This operator used to fetch the records with value match with the given filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1, 5).where("ShipCountry", "IN", ['INDIA'], false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>
notin
This operator used to fetch the records with value not match with the given filter value.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
var dataManager = ej.DataManager({
url: "http://mvc.syncfusion.com/Services/Northwnd.svc/"
});
var query = ej.Query()
.from("Orders").select("OrderID", "CustomerID", " EmployeeID", "Freight", "ShipCountry")
.page(1, 5).where("ShipCountry", "notin", ['INDIA'], false);
var execute = dataManager.executeQuery(query) // executing query
.done(function (e) {
$("#table1 tbody").html($("#tableTemplate").render(e.result));
});
});
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>
Lambda Operators
all
The ALL operator returns true if all the sub query values meet the condition. This operator used to fetch the records of the fields match with the given value’s fields.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
var gridData=[{OrderID:10248,CustomerID:"VINET",EmployeeID:5,OrderDate:new Date(8364186e5),ShipName:"Vins et alcools Chevalier",ShipCity:"Reims",ShipAddress:"59 rue de l'Abbaye",ShipRegion:null,ShipPostalCode:"51100",ShipCountry:"France",Freight:32.38,Verified:!0}];
var Data= [{OrderID:10248,CustomerID:"VINET",EmployeeID:5,OrderDate:new Date(8364186e5),ShipName:"Vins et alcools Chevalier",ShipCity:"Reims",ShipAddress:"59 rue de l'Abbaye",ShipRegion:null,ShipPostalCode:"51100",ShipCountry:"France",Freight:32.38,Verified:!0}];
var dataManager = ej.DataManager(gridData);
var source = ej.DataManager(Data);
var result = ej.Query().select("ShipCountry")
var ShipCountry = source.executeLocal(result);
var query = ej.Query()
.where("ShipCountry", "equal all",ShipCountry , false).select("OrderID", "CustomerID", "EmployeeID","Freight","ShipCountry");
var execute = dataManager.executeLocal(query); // executing query
$("#table1 tbody").html($("#tableTemplate").render(execute)); });
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>
any
The ANY operator returns true if any of the sub query values meet the condition. The returned data match with any one of the field in existing table.
<div class="datatable">
<table id="table1" class=" table table-striped table-bordered" style="width:700px">
<thead>
<tr>
<th>Order ID</th>
<th>Customer ID</th>
<th>Employee ID</th>
<th>Freight</th>
<th>Ship Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
<script type="text/javascript">
$(function () {// Document is ready.
var gridData = window.gridData;
var data = [{ OrderID: 10343, CustomerID: "LEHMS", EmployeeID: 4, OrderDate: new Date(8467002e5), ShipName: "Lehmanns Marktstand", ShipCity: "Frankfurt a.M.", ShipAddress: "Magazinweg 7", ShipRegion: null, ShipPostalCode: "60528", ShipCountry: "Germany", Freight: 110.37, Verified: !0 }, { OrderID: 10344, CustomerID: "WHITC", EmployeeID: 4, OrderDate: new Date(8467866e5), ShipName: "White Clover Markets", ShipCity: "Seattle", ShipAddress: "1029 - 12th Ave. S.", ShipRegion: "WA", ShipPostalCode: "98124", ShipCountry: "USA", Freight: 23.29, Verified: !1 }];
var dataManager = ej.DataManager(data);
var source = ej.DataManager(gridData1);
var result = ej.Query().select("ShipCountry")
var ShipCountry = source.executeLocal(result);
var query = ej.Query()
.where("ShipCountry", "equal any", ShipCountry, false).select("OrderID", "CustomerID", "EmployeeID", "Freight", "ShipCountry");
var execute = dataManager.executeLocal(query); // executing query
$("#table1 tbody").html($("#tableTemplate").render(execute));
}
</script>
<script id="tableTemplate" type="text/x-jsrender">
<tr>
<td>{{>OrderID}}</td>
<td>{{>CustomerID}}</td>
<td>{{>EmployeeID}}</td>
<td>{{>Freight}}</td>
<td>{{>ShipCountry}}</td>
</tr>
</script>