In this post we'll build an Angular web application that demonstrates how to use the AG Grid Server-side Row Model with Laravel in the backend using a MySQL database. We'll walk you through how to install the different elements of this solution and how to implement grouping, sorting, filtering and aggregations using the AG Grid server-side row model which loads data in pages from the server, which is especially useful for large datasets.
See the sample application in action demonstrating AG Grid with Server-side filtering, grouping and pagination below:
Contents
- Overview
- Installation
- Building the RESTful API
- Retrieving Rows
(POST api/olympicWinners) - Asynchronous Set Filter Values
(GET api/olympicWinners/{field})
Overview
We will show how to implement the following features in the Angular application using AG Grid: Grouping, Filtering, Aggregating, Sorting and Fetching Asynchronous Set Filter Values.
To keep this blog post short, we will not be covering the implementation of the Angular client, however you can find the implementation here.
Installation
Please install and run the application using the instructions here.
Building the RESTful API
Now that you've installed the different components, let's look at how to build a RESTful API to handle the requests from the client.
We define the server endpoints in the routes/api.php file because these endpoints are stateless and are assigned the api middleware group.
// server/routes/api.php
Route::post('olympicWinners', [AthleteController::class, 'getData']);
Route::get('olympicWinners/{field}', [AthleteController::class, 'getSetFilterValues']);As you can see from the above snippet, we've created two endpoints:
-
olympicWinners- accepts POST requests. This endpoint will handle the requests from thegetRowsmethod inside the Server-side data source olympicWinners/{field}- accepts GET requests and requires afieldparameter to be provided. This is necessary so we can return the Set Filter values for the filtered column, so we can load them asynchronously. Read more about providing set filter values asynchronously in our documentation.
Both of these routes have controller methods getData and getSetFilterValue provided, which we will look at in the following sections.
Retrieving Rows
Now that we've configured the backend and the RESTful API, let's take a look at how to load rows into AG Grid. Rows are given to the grid by the endpoint POST api/olympicWinners which is handled by the controller method getData.
The code snippet below shows how we are building and executing queries against our SQL database and getting rows and the total row count in response:
// server/app/Http/Controllers/AthleteController.php
public function getData(Request $request)
{
$SQL = $this->buildSql($request);
$results = DB::select($SQL);
$rowCount = $this->getRowCount($request, $results);
$resultsForPage = $this->cutResultsToPageSize($request, $results);
return ['rows' => $resultsForPage, 'lastRow' => $rowCount];
}
public function buildSql(Request $request)
{
$selectSql = $this->createSelectSql($request);
$fromSql = " FROM ATHLETES ";
$whereSql = $this->whereSql($request);
$groupBySql = $this->groupBySql($request);
$orderBySql = $this->orderBySql($request);
$limitSql = $this->createLimitSql($request);
$SQL = $selectSql . $fromSql . $whereSql . $groupBySql . $orderBySql . $limitSql;
return $SQL;
}In the buildSql method above, you can see we've separated each query clause as a separate string to be concatenated into the final query.
The following sections will look at the methods which implement valuable functionality such as grouping, sorting, filtering, aggregation, etc.
Grouping
Let's first implement support for row grouping. First, we check whether any of the grid columns are grouped using the function isDoingGrouping and if they are, the grouped columns are appended to the GROUP BY clause as shown below:
// server/app/Http/Controllers/AthleteController.php
public function groupBySql(Request $request)
{
$rowGroupCols = $request->input('rowGroupCols');
$groupKeys = $request->input('groupKeys');
if ($this->isDoingGrouping($rowGroupCols, $groupKeys)) {
$colsToGroupBy = [];
$rowGroupCol = $rowGroupCols[sizeof($groupKeys)];
array_push($colsToGroupBy, $rowGroupCol['field']);
return " GROUP BY " . join(", ", $colsToGroupBy);
} else {
// select all columns
return "";
}
}
public function isDoingGrouping($rowGroupCols, $groupKeys)
{
// we are not doing grouping if at the lowest level. we are at the lowest level
// if we are grouping by more columns than we have keys for (that means the user
// has not expanded a lowest level group, OR we are not grouping at all).
return sizeof($rowGroupCols) > sizeof($groupKeys);
}With this implementation we can now group multiple columns like country and year as shown below:
Sorting
Row sorting is key functionality for any table, so we'll continue with this. When you sort any of the columns in AG Grid, a sortModel object is updated to store the sorting configuration. In order to add the sorting clause to the backend query, we iterate through the request.sortModel object and add each colId to an array which are then appended to the ORDER BY clause:
// server/app/Http/Controllers/AthleteController.php
public function orderBySql(Request $request)
{
$sortModel = $request->input('sortModel');
if ($sortModel) {
$sortParts = [];
foreach ($sortModel as $key => $value) {
array_push($sortParts, $value['colId'] . " " . $value['sort']);
}
if (sizeof($sortParts) > 0) {
return " ORDER BY " . join(", ", $sortParts);
} else {
return '';
}
}
}With this implementation, we can now sort on any column as well as multi-sort as shown here:
Filtering
Any large data set needs to be filtered to help the user find the records they looking for.
The AG Grid filterModel stores the filtering configuration at any one point. Our example adds filtering support for the set filters used in AG Grid, so we start by checking whether the request.filterModel object contains filterType: 'set'. This way we extract the columns filtered using a set filter in the grid's filterModel and append them to the WHERE clause as shown below:
// server/app/Http/Controllers/AthleteController.php
public function whereSql(Request $request)
{
$rowGroupCols = $request->input('rowGroupCols');
$groupKeys = $request->input('groupKeys');
$filterModel = $request->input('filterModel');
$whereParts = [];
if (sizeof($groupKeys) > 0) {
foreach ($groupKeys as $key => $value) {
$colName = $rowGroupCols[$key]['field'];
array_push($whereParts, "{$colName} = '{$value}'");
}
}
if ($filterModel) {
foreach ($filterModel as $key => $value) {
if ($value['filterType'] == 'set') {
array_push($whereParts, $key . ' IN ("' . join('", "', $value['values']) . '")');
}
}
}
if (sizeof($whereParts) > 0) {
return " WHERE " . join(' and ', $whereParts);
} else {
return "";
}
}Once the filtering logic is implemented, we can now filter on columns which have a Set Filter:
Aggregation
Now that we have grouping implemented, let's add value aggregation. We are only aggregating on grouped columns for this example. We start by checking if the columns are being grouped by calling the function isDoingGrouping and we append each group column to the SELECT clause as well as a column for each aggregation column.
// server/app/Http/Controllers/AthleteController.php
public function createSelectSql(Request $request)
{
$rowGroupCols = $request->input('rowGroupCols');
$valueCols = $request->input('valueCols');
$groupKeys = $request->input('groupKeys');
if ($this->isDoingGrouping($rowGroupCols, $groupKeys)) {
$colsToSelect = [];
$rowGroupCol = $rowGroupCols[sizeof($groupKeys)];
array_push($colsToSelect, $rowGroupCol['field']);
foreach ($valueCols as $key => $value) {
array_push($colsToSelect, $value['aggFunc'] . '(' . $value['field'] . ') as ' . $value['field']);
}
return "SELECT " . join(", ", $colsToSelect);
}
return "SELECT * ";
}Now that we have this code in place, we can aggregate the non-grouped columns via the column menu as shown below:
Asynchronous Set Filter Values
By default set filter values are provided synchronously, but some scenarios require providing them asynchronously. Let's now illustrate how to load the set filter values in the AG Grid column filter asynchronously.
The set filter values are provided by the endpoint GET api/olympicWinner/{field} which is handled by the controller method getSetFilterValues. The code snippet below shows how we are executing queries to our SQL database.
We use the Laravel Query Builder to retrieve the set filter values. We need to return the distinct values which match the given field in ascending sort order. See this implemented below:
// server/app/Http/Controllers/AthleteController.php
public function getSetFilterValues(Request $request, $field)
{
$values = DB::table('athletes')->select($field)->distinct()->orderBy($field, 'asc')->pluck($field);
return $values;
}With this implementation, our set filter values are now loaded asynchronously as shown below:
What's next?
We hope that you find this article useful when using AG Grid with server-side row model together with Angular, Laravel and MySQL. The sample above showed how to install the different components as well as set up AG Grid to use the server-side row model to implement grouping, sorting, column filtering, and aggregations. You can download the project from GitHub and easily reuse the code I've described above to implement this in your project.
If you would like to try out AG Grid check out our getting started guides (JS / React / Angular / Vue)
Happy coding!