In this post we'll demonstrate how to implement server-side tree data in ag-Grid using the MEVN stack (MongoDB + Express + Vue.js + Node.js). We'll show you how to set up the environment and implement the backend operations to return data for the server-side row model grouping, filtering, sorting and aggregation settings in ag-Grid.
You can download the entire project from GitHub and easily reuse the code and approach to implement this in your project.
See the sample in action demonstrating ag-Grid with server-side filtering, sorting and aggregations:
We'll be going over:
- Installation
- Initialising a MongoDB Collection
- Building the RESTful API
- Retrieving rows
(POST api/filesystem) - Querying plain data
- Building our data retrieval code
- Grouping
- Aggregations
- Filtering
- Sorting
- Loading data in blocks to support infinite scrolling
- Front-end code
Installation
Please download the GitHub repository, clone it, and run the following script to install all dependencies:
$ npm install && npm client install Note: If you don't already have mongodb community edition v4.4 installed, please install it here.
Start a local mongo service by running:
$ npm run mongo-service-startLastly run the following script to start both the backend and client servers:
$ npm run devInitialising a MongoDB Collection
Now that we've installed Mongodb, let's initialize a MongoDB collection. We're using Mongoose to connect to our local MongoDB service and
initialise a collection named 'filesystem' in a new database called 'acme', as shown in the code below:
const db = process.env.MONGODB_URL;
const mongoose = require('mongoose');
mongoose
.connect(db, {
useNewUrlParser: true,
useUnifiedTopology: true
})
.then(() => {
console.log('MongoDB connected database "acme"');
// populate fileSystem collection
const initFileSystemCollection = require('./init');
initFileSystemCollection();
})
.catch(err => console.log('MongoDB connection error: ' + err));Each document in the filesystem collection will be applying the following Schema:
const FileSystemSchema = new Schema();
// recursive references must be added like this
FileSystemSchema.add({
folder: String,
dateModified: Date,
size: Number,
subFolders: [FileSystemSchema]
});In the segment above, note that the FileSystem schema has a recursive structure. Documents may contain an arrays of sub-documents that also follow the FileSystem schema under the subFolders property.
Now that we've initialized the collection, we can query it via MongoDB Compass as shown below:
Building the RESTful API
Now that we've built the data storage layer, let's build the backend services to expose it to our application. Let's add the following endpoints to handle our API requests:
POST api/filesystemretrieves documents.GET api/filesystem/values/:fieldreturns all unique values for a field in theFileSystemSchema.
Both of these web service endpoints will be accessed from our serverSideDatasource to get the data from the database for the specific grouping, sorting, filtering, aggregation parameters set in ag-Grid.
Retrieving rows (POST api/filesystem)
Let's now take a look specifically at how to retrieve rows from our database. The request body includes a data object with the following properties:
let {
startRow,
endRow,
groupKeys = [],
sortModel = [],
filterModel = {},
valueCols = []
} = req.body.data;Let's now use these properties to return the correct rows from our database to ag-Grid using the simplest possible scenario, where rows are requested without any grouping, aggregations, filtering or sorting.
Querying plain data
Let's now build a query to return all rows within a range without any grouping, filtering, sorting or aggregations. In order to return a row range, our server endpoint API needs to handle the startRow and endRow parameters.
See the server endpoint code shown below (explanation below):
FileSystem
.find({})
.skip(startRow)
.limit(endRow - startRow)
.exec((err, rows) => {
if (err) {
console.log('error in query', err);
// handler error*****
}
let lastRowIndex = getLastRowIndex(startRow, endRow, rows);
res.json({
rows,
lastRowIndex
});
})As shown in the code above, we use the .find({}) method to return all documents in the FileSystem collection. We then chain on the .skip() and .limit() methods to slice out the row range requested by ag-Grid. Lastly we calculate the lastRow index, which is used by ag-Grid to configure our grid instance's vertical scrollbar.
Once the query is executed, the rows are displayed in the ag-Grid instance as shown below (see the request and response on the right):
Now that we've covered the simplest case, let's demonstrate how to create a MongoDB query when our ag-Grid instance applies grouping, sorting, filtering and aggregations.
Building our data retrieval code
Now that we've looked at the simplest possible query, let's build a Mongoose query that will allow us to group, filter, sort, aggregate and slice the resulting data into pages to be loaded on demand. We'll do this with a series of steps each adding code to handle a different data operation. For this we'll be using MongoDb aggregations and Mongoose methods on our file system model to build a multi-stage query.
In the end, this will be executed as a single query with a number of parameters specifying the sort order, group field, filter parameters and so on.
Grouping
Let's start by building code to retrieve a grouped set of records from our MongoDb collection. We begin by building an aggregation pipeline (not to be confused with ag-Grid aggregations) that MongoDB will use to request data from the filesystem collection.
When a group node is expanded and ag-Grid requests new rows, a groupKeys array is included in the request object. The array holds the ID of the expanded node and the ID's of all its parent/grandparent node(s).
For each group key in the groupKeys array we add the following 3 aggregation stages to our aggregation pipeline:
$match- finds the document that corresponds to the expanded group$unwind- deconstructs the document'ssubFolderarray so that a new record is created at the root level for each element in the array under the property namesubFolder$replaceroot- replaces each document in the pipeline with the sub-document created in the previous step.
The result of these aggregation steps will return the leaf nodes of the expanded group.
const aggregationPipeline = [];
const isGrouping = groupKeys.length > 0;
if (isGrouping) {
groupKeys.forEach(groupKey => {
aggregationPipeline.push({
'$match': {
'_id': new mongoose.Types.ObjectId(groupKey)
}
}, {
'$unwind': {
'path': '$subFolders'
}
}, {
'$replaceRoot': {
'newRoot': '$subFolders'
}
})
});
}Once this is executed, ag-Grid displays the grouped set of records as shown below:
Aggregations
Let's now add aggregations to this grouped record set in ag-Grid. In our demo grid, we are applying a sum aggregation on the 'size' column. We can calculate these aggregations on our server by adding the following code to the MongoDB aggregation pipeline (explained below):
const isAggregating = Object.keys(valueCols).length > 0;
if (isAggregating) {
aggregationPipeline.push({
'$addFields': {
'size': {
'$cond': {
'if': {
'$gt': [
{
'$size': '$subFolders'
}, 0
]
},
'then': {
'$function': {
'body': `
function (subFolders) {
function getSize(subFolders) {
let total = 0;
subFolders.forEach(subFolder => {
if (subFolder.hasOwnProperty(\'size\')) {
total += subFolder.size;
} else {
total += getSize(subFolder.subFolders)
}
});
return total;
}
return getSize(subFolders)
}
`,
'args': [
'$subFolders'
],
'lang': 'js'
}
},
'else': '$size'
}
}
}
}
)
}In the code above, we use the $addFields aggregation stage to add a 'size' property to each document in the pipeline. If a document is a group then we calculate a total value by recursively traversing its subFolders tree. If the document is not a group then we simply return its pre-existing size property. We use the $cond expression to apply this conditional logic.
Once this executes, ag-Grid displays the aggregated set of records as shown below:
Filtering
Let's now take a look at how we can add filtering to our query.
The code below in our endpoint implementation is used to filter documents in our MongoDB aggregation pipeline (explained further below):
if (isFiltering) {
let filterValues = filterModel.folder.values;
aggregationPipeline.push(
{
"$addFields": { filterValues }
},
{
"$match": {
$expr: {
$function: {
body: `
function (filterValues, subFolders, folder) {
function doesDocPassFilter(doc) {
return filterValues.includes(doc.folder)
}
function recursivelyScanDoc(doc) {
if (doesDocPassFilter(doc)) {
return true;
}
if (!doc.hasOwnProperty('subFolders')) {
return false;
}
return doc.subFolders.some(doc => recursivelyScanDoc(doc));
}
if (filterValues.length === 0) {
return null
}
let doc = {
folder,
subFolders
}
return recursivelyScanDoc(doc);
}
`,
args: ["$filterValues", "$subFolders", "$folder"],
lang: "js"
}
}
}
},
{ "$unset": "filterValues" }
)
}The request object includes a filterModel object that contains the values being filtered on the ag-Grid UI. We add these filteredValues directly onto each document in the aggregation pipeline. Note that this added property will get removed after we've finished filtering.
We then use the $match stage and $function operator to inject a function to filter any documents that either pass the filter test themselves or have a sub-document somewhere within their subFolders tree that pass the filter test.
We then remove the filteredValues property from the filtered documents in the $unset stage.
Once this backend code is implemented, ag-Grid can filter records as shown below:
Sorting
Let's now look at how we can add sorting to this query. The request object includes a sortModel array that contains the sorting state for every sorted column in the ag-Grid instance.
The following code allows us to sort documents in our database query according to the sortModel (explainde further below):
const isSorting = sortModel.length > 0;
if (isSorting) {
const sortQuery = {};
sortModel.forEach(({ colId, sort }) => {
sortQuery[colId] = sort;
});
query
.sort(sortQuery)
.collation({
locale: 'en',
numericOrdering: true
});
}
First we chain Mongoose's .sort() method onto our database query, passing a sortQuery object as a parameter. We then chain the .collation() method, which allows sorting for fields that contain strings and numbers (such as the folder field). The numericOrdering property setting allows you to have any numbers sorted in a numeric instead of literal sort order.
Once this code executes, it returns a sorted set of records which is displayed in ag-Grid as shown below:
Loading data in blocks to support infinite scrolling
The query we've built up until now to implement the grouping, sorting, filtering, aggregations above will return the entire set of matched records. However, this may be too many records to handle on the client at a time. Ag-grid allows to handle this case via the server-side row model, which requests records in blocks from the backend based on the vertical scroll position.
This is why we now need to modify our MongoDb query to return blocks of rows to the client to better support large datasets.
This means we need to slice out the range of documents requested by ag-Grid. This way we can return only the requested range of records by ag-Grid.
To do this we chain Mongoose's .skip() and .limit() methods onto our query, as shown below:
query
.skip(startRow)
.limit(endRow - startRow)
.exec((err, rows) => {
if (err) {
console.log('error in query', err);
// handler error*****
}
let lastRowIndex = getLastRowIndex(startRow, endRow, rows);
res.json({
rows,
lastRowIndex
});
})
Now we're ready to finally execute our query we've been building. Once the range of rows has been returned, we package them in a HTTP response back to the client that holds:
- The requested rows
- a
lastRowIndexproperty - which will be used by ag-Grid to configure our grid instances vertical scrollbar height.
This will now allow ag-Grid to effectively handle large amounts of data by loading it in blocks of rows on demand as the user is scrolling down.
Front-end code
We've omitted an explanation of the front-end involved to keep this blogpost short. However, you can see the full source code in the GitHub repo for this sample. It'll show you how you can activate server-side row model grouping, sorting, filtering, etc in ag-Grid to provide the needed parameters to the request which is then executed by the query above. You can read more about how to use the server-side row model in our documentation which covers how to implement all server-side operations.
What's next?
We hope that you find this article useful when building your ag-Grid projects with the MEVN stack. Remember 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!