Create a Smart Collection

What is a Smart Collection?

A Smart Collection is a Forest Collection based on your API implementation. It allows you to reconcile fields of data coming from different or external sources in a single tabular view (by default), without having to physically store them into your database.

Fields of data could be coming from many other sources such as other B2B SaaS (e.g. Zendesk, Salesforce, Stripe), in-memory database, message broker, etc.

This is an advanced notion. If you're just starting with Forest Admin, you should skip this for now.

In the following example, we have created a Smart Collection called customer_statsallowing us to see all customers who have placed orders, the number of order placed and the total amount of those orders.

For an example of advanced customisation and featuring an Amazon S3 integration, you can see here how we've stored in our live demo the companies' legal documents on Amazon S3 and how we've implemented a Smart Collection to access and manipulate them.

Creating a Smart Collection

SQL
Mongodb

First, we declare the customer_stats collection in the forest/ directory.

In this Smart Collection, we want to display for each customer its email address, the number of orders made (in a field orders_count) and the sum of the price of all those orders (in a field total_amount).

You can check out the list of available field options if you need it.

You MUST declare an id field when creating a Smart Collection. The value of this field for each record MUST be unique.

As we are using the customer id in this example, we do not need to declare an id manually.

forest/customer_stats.js
const { collection } = require('forest-express-sequelize');
const models = require('../models');
collection('customer_stats', {
isSearchable: true,
fields: [{
field: 'email',
type: 'String',
}, {
field: 'orders_count',
type: 'Number',
}, {
field: 'total_amount',
type: 'Number',
}],
});

The optionisSearchable: true added to your collection allows to display the search bar. Note that you will have to implement the search yourself by including it into your own get logic.

Work in progress - this section will soon be released

Implementing the GET (all records)

At this time, there’s no Smart Collection Implementation because no route in your app handles the API call yet.

SQL
Mongodb

In the file routes/customer_stats.js, we’ve created a new route to implement the API behind the Smart Collection.

The logic here is to list all the customers that have made orders (with their email), to count the number of orders made and to sum up the price of all the orders.

The limit and offset variables are used to paginate your collection according to the number of records per page set in your UI.

We have implemented a search logic to catch if a search query (accessible through req.query.search) has been performed and to return all records for which the email field matches the search.

Finally, the last step is to serialize the response data in the expected format which is simply a standard JSON API document. We use the very simple JSON API Serializer library for this task.

/routes/customer_stats.js
const Liana = require('forest-express-sequelize');
const express = require('express');
const router = express.Router();
const models = require('../models');
const P = require('bluebird');
const JSONAPISerializer = require('jsonapi-serializer').Serializer
router.get('/customer_stats', (req, res, next) => {
const limit = parseInt(req.query.page.size) || 20;
const offset = (parseInt(req.query.page.number) - 1) * limit;
const queryType = models.sequelize.QueryTypes.SELECT;
let conditionSearch = '';
if (req.query.search) {
conditionSearch = `customers.email LIKE '%${req.query.search.replace(/\'/g, '\'\'')}%'`;
}
const queryData = `
SELECT customers.id,
customers.email,
count(orders.*) AS orders_count,
sum(products.price) AS total_amount,
customers.created_at,
customers.updated_at
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN products ON orders.product_id = products.id
${conditionSearch ? `WHERE ${conditionSearch}` : ''}
GROUP BY customers.id
ORDER BY customers.id
LIMIT ${limit}
OFFSET ${offset}
`;
const queryCount = `
SELECT COUNT(*)
FROM customers
WHERE
EXISTS (
SELECT *
FROM orders
WHERE orders.customer_id = customers.id
)
${conditionSearch ? `AND ${conditionSearch}` : ''}
`;
return P
.all([
models.sequelize.query(queryData, { type: queryType }),
models.sequelize.query(queryCount, { type: queryType }),
])
.spread((customerStatsList, customerStatsCount) => {
const customerStatsSerializer = new JSONAPISerializer('customer_stats', {
attributes: ['email', 'orders_count', 'total_amount'],
keyForAttribute: 'underscore_case'
});
const customerStats = customerStatsSerializer.serialize(customerStatsList);
const count = customerStatsCount[0].count
res.send({ ...customerStats, meta:{ count: count }});
})
.catch((err) => next(err));
});
module.exports = router;

Work in progress - this section will soon be released

Now we are all set, we can access the Smart Collection as any other collection.

In this example we have only implemented the GET all records action but you can also add the following actions: GET specific records, PUT, DELETE and POST. These are shown in the next page explaining how a Smart Collection can be used to access and manipulate data stored in Amazon S3.