Create a Smart Collection

By default, Forest Admin creates one collection per table available from your application's database. But you might want to display custom data from several different collections or external sources such as Amazon S3, Redis, on elsewhere.

A Smart Collection allows you to create a Forest Admin collection and implement all the logic to access and/or manipulate it.

In the following example, we have created a view of all customers who have placed orders, the number of order placed and the total amount of those orders through the implementation of a Smart Collection.

Declare a Smart Collection

Lumber
Rails
Express/Sequelize
Express/Mongoose

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 MUST declare an id field when creating a Smart Collection. The value of this field for each record MUST be unique. In the following example, we are using the customer id so we do not need to manually declare an id field.

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

First, we declare the CustomerStat collection in the lib/forest-liana/collections/ 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 MUST declare an id field when creating a Smart Collection. The value of this field for each record MUST be unique. In the following example, we are using the customer id so we do not need to manually declare an id field.

lib/forest-liana/collections/customer-stat.rb
class Forest::CustomerStat
include ForestLiana::Collection
collection :CustomerStat
field :id, type: 'Number', is_read_only: true
field :email, type: 'String', is_read_only: true
field :orders_count, type: 'Number', is_read_only: true
field :total_amount, type: 'Number', is_read_only: true
end

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 MUST declare an id field when creating a Smart Collection. The value of this field for each record MUST be unique. In the following example, we are using the customer id so we do not need to manually declare an id field.

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

Then, after restarting your back office server and refreshing the UI, we'll see the new collection available from the navigation bar. At this point, we cannot access it because we didn't implement the logic yet.

Implement the GET (all records) of a Smart Collection

Of course, the implementation totally depends on the data nature and where you've stored it. This is the code we use on this example:

Lumber
Rails
Express/Sequelize
Express/Mongoose
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', Liana.ensureAuthenticated, (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;
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
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
)
`;
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;

We use the NPM package bluebird. This service requires jsonapi-serializer. Of course, the required dependencies depends entirely on your own implementation.

lib/forest-liana/controllers/customer_stats_controller.rb
class Forest::CustomerStatsController < ForestLiana::ApplicationController
require 'jsonapi-serializers'
before_action :set_params, only: [:index]
class BaseSerializer
include JSONAPI::Serializer
def type
'customerStat'
end
def format_name(attribute_name)
attribute_name.to_s.underscore
end
def unformat_name(attribute_name)
attribute_name.to_s.dasherize
end
end
class CustomerStatSerializer < BaseSerializer
attribute :email
attribute :total_amount
attribute :orders_count
end
def index
customers_count = Customer.count_by_sql("
SELECT COUNT(*)
FROM customers
WHERE
EXISTS (
SELECT *
FROM orders
WHERE orders.customer_id = customers.id
)
")
customer_stats = Customer.find_by_sql("
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
GROUP BY customers.id
ORDER BY customers.id
LIMIT #{@limit}
OFFSET #{@offset}
")
customer_stats_json = CustomerStatSerializer.serialize(customer_stats, is_collection: true, meta: {count: customers_count})
render json: customer_stats_json
end
private
def set_params
@limit = params[:page][:size].to_i
@offset = (params[:page][:number].to_i - 1) * @limit
end
end

As the response data sent is expected to be formatted as a standard JSON API document, we have used the JSON API Serializer library for this task in this example.

Finally you need to create a route pointing to your collection's index action to get all your collection's records.

config/routes.rb
Rails.application.routes.draw do
# MUST be declared before the mount ForestLiana::Engine.
namespace :forest do
get '/CustomerStat' => 'customer_stats#index'
end
mount ForestLiana::Engine => '/forest'
end
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', Liana.ensureAuthenticated, (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;
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
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
)
`;
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;

We use the NPM package bluebird. This service requires jsonapi-serializer. Of course, the required dependencies depends entirely on your own implementation.

Congrats, this step is now successfully completed! 🎉