Grid with querybuilder server-side
4 Replies
Hi Rafael,
Greetings from syncfusion support
Query: I need to page the data in server-side mode because we have a lot of data in some cases.
We have validated your query at our end. Before proceeding with your query we would like to share the available Data-Adaptors in the Grid. You can choose the adaptors based on your application. Refer to the below documentation which illustrates available data adaptors in EJ2.
For each Grid data-action like Filtering, Sorting, Paging etc., we send the correspond query to the server based on the data-adaptors. You can execute the queries with your data and return results with required format to the Grid. Find the below screenshots for your reference.
Screenshot #1: Queries on ODataAdaptor
Screenshot #2: Queries ODataV4Adaptor
Screenshot #3: Queries on WebApiAdaptor
Screenshot #4: Queries on UrlAdaptor (on-demand)
Query#: we have a legacy project that the names of columns in SQL Server is different than the names of properties in class
We have checked your reported query and it can be achieved by using PredicateBuilder method. We have created Querybuilder sample as angular application and created service in ASP.Net core. In the service we have used local database which is used for performing query builder filter. We have passed the created predicates to service to perform the filtering with database. Please refer below code snippets.
App.Component.html
|
<div>
<button class="e-btn" id="getFilteredRecords"(click)="getFilteredRecords($event)">Get Records</button>
<ejs-querybuilder [dataSource]="data" id="querybuilder" #querybuilder width="100%" [rule] = "importRules">
</ejs-querybuilder>
<div id="content_section" class="e-content-section e-hide">
<table id="datatable" class='e-rule-table'>
<thead>
<tr>
<th>EmployeeID</th>
<th>FirstName</th>
<th>LastName</th>
<th>Title</th>
<th>Address</th>
<th>Country</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
</div
|
App.Component.ts
|
export class AppComponent implements OnInit {
@ViewChild('querybuilder')qryBldrObj: QueryBuilderComponent;
public importRules: RuleModel;
public data: DataManager;
public getFilteredRecords = (): void => {
let template: string = '<tr><td>${EmployeeID}</td><td>${LastName}</td><td>${FirstName}</td><td>${Title}</td><td>${Address}</td><td>${Country}</td></tr>';
let compiledFunction: any = compile(template);
let table: HTMLElement = document.getElementById('datatable') as HTMLElement;
var QueryData = this.qryBldrObj.getPredicate(this.qryBldrObj.getValidRules());
var rootFilter = []; rootFilter.push(QueryData);
var filter = { 'where': rootFilter};
let url: any = new URL('https://localhost:44376/employee');
(url as any).search = new URLSearchParams({search:JSON.stringify(filter)});
fetch(url).then(response =>
response.json()
).then(data => {
if (data.length) {
document.getElementById('content_section').classList.remove('e-hide');
} else {
document.getElementById('content_section').classList.add('e-hide');
}
table.querySelector('tbody').innerHTML = '';
data.forEach((item) => {
table.querySelector('tbody').appendChild(compiledFunction(item)[0].querySelector('tr'));
});
});
};
ngOnInit(): void {
this.data = new DataManager({
url: 'https://localhost:44376/employee',
adaptor: new WebApiAdaptor(),
crossDomain: true
});
this.importRules = {
'condition': 'and',
'rules': [{
'label': 'Employee ID',
'field': 'EmployeeID',
'type': 'number',
'operator': 'equal',
'value': 1
}]
};
}
}
|
Service
|
[HttpGet]
public IEnumerable<Employee> Get(string search)
{
IEnumerable<Employee> employees = _context.Employees.ToList();
IEnumerable<NameList> nameList;
if (search != null)
{
var FilterQuery = JsonConvert.DeserializeObject<WhereData>(search);
if (FilterQuery.Where != null && FilterQuery.Where.Count > 0)
{
Type type = _context.Employees.GetElementType();
if (type == null)
{
Type type1 = _context.Employees.GetType();
type = type1.GetElementType();
}
var paramExpression = type.Parameter();
QueryableOperation operation = new QueryableOperation();
var predicates = operation.PredicateBuilder(_context.Employees, FilterQuery.Where, FilterQuery.Where[0].Condition, paramExpression, type);
IEnumerable<Employee> whereData = (IEnumerable<Employee>)_context.Employees.AsQueryable().Where(paramExpression, predicates);
employees = whereData.Select(emp => new Employee
{
EmployeeID = emp.EmployeeID,
LastName = emp.LastName,
FirstName = emp.FirstName,
Title = emp.Title,
Address = emp.Address,
Country = emp.Country
});
}
}
return employees;
}
} |
For your reference, we have prepared a sample based on this. Please refer below link.
Sample Link: https://www.syncfusion.com/downloads/support/forum/160811/ze/angular-querybuilder-1271998057
We have created the video demonstration based on this. Please find the below video link.
Link: https://www.syncfusion.com/downloads/support/forum/160811/ze/QuerybuilderWithLinqQuerys1397090298
Note: You can create your own local database (EmployeeDb) and table (Employees) to run the service.
Please let us know, if you need any further assistance.
Regards,
Rajapandi R
SIGN IN To post a reply.
