v8 (v7 Rails v1 Django)
Smart Collections
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
Rails
Django
Laravel
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.As we are using the customer id in this example, we do not need to declare an
id manually.forest/customer_stats.js
1
const { collection } = require('forest-express-sequelize');
2
const models = require('../models');
3
4
collection('customer_stats', {
5
isSearchable: true,
6
fields: [{
7
field: 'email',
8
type: 'String',
9
}, {
10
field: 'orders_count',
11
type: 'Number',
12
}, {
13
field: 'total_amount',
14
type: 'Number',
15
}],
16
17
});
Copied!
The option
isSearchable: 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
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.As we are using the customer id in this example, we do not need to declare an
id manually.lib/forest-liana/collections/customer_stat.rb
1
class Forest::CustomerStat
2
include ForestLiana::Collection
3
4
collection :CustomerStat, is_searchable: true
5
6
field :id, type: 'Number', is_read_only: true
7
field :email, type: 'String', is_read_only: true
8
field :orders_count, type: 'Number', is_read_only: true
9
field :total_amount, type: 'Number', is_read_only: true
10
11
end
Copied!
The option
is_searchable: 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 in your collection controller.First, we declare the
CustomerStat collection in the app/forest/customer_stat.py file.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.As we are using the customer id in this example, we do not need to declare an
idapp/forest/customer_stats.py
1
from django_forest.utils.collection import Collection
2
3
4
class CustomerStat(Collection):
5
6
is_searchable = True
7
8
def load(self):
9
self.name = 'CustomerStat'
10
self.fields = [
11
{
12
'field': 'id',
13
'type': 'Number',
14
},
15
{
16
'field': 'email',
17
'type': 'String'
18
},
19
{
20
'field': 'orders_count',
21
'type': 'Number'
22
},
23
{
24
'field': 'total_count',
25
'type': 'Number'
26
}
27
]
28
29
30
Collection.register(CustomerStat)
Copied!
The option
is_searchable = 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 in your collection controller.First, we declare the
CustomerStat collection in the app/Models/SmartCollections/CustomerStat.php file.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.As we are using the customer id in this example, we do not need to declare an
idapp/Models/SmartCollections/CustomerStat.php
1
<?php
2
3
namespace App\Models\SmartCollections;
4
5
use ForestAdmin\LaravelForestAdmin\Services\SmartFeatures\SmartCollection;
6
use ForestAdmin\LaravelForestAdmin\Services\SmartFeatures\SmartField;
7
use Illuminate\Support\Collection;
8
9
class CustomerStat extends SmartCollection
10
{
11
protected string $name = 'customerStat';
12
13
protected bool $is_searchable = true;
14
15
protected bool $is_read_only = true;
16
17
/**
18
* @return Collection
19
*/
20
public function fields(): Collection
21
{
22
return collect(
23
[
24
new SmartField(
25
[
26
'field' => 'id',
27
'type' => 'Number',
28
]
29
),
30
new SmartField(
31
[
32
'field' => 'email',
33
'type' => 'String',
34
]
35
),
36
new SmartField(
37
[
38
'field' => 'orders_count',
39
'type' => 'Number',
40
]
41
),
42
new SmartField(
43
[
44
'field' => 'total_count',
45
'type' => 'Number',
46
]
47
),
48
]
49
);
50
}
51
}
Copied!
The option
is_searchable = 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 in your collection controller.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
Rails
Django
Laravel
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. A class called RecordSerializer is made available to help you serialize the records. You can read more about this class here.
/routes/customer_stats.js
1
const { RecordSerializer } = require('forest-express-sequelize');
2
const express = require('express');
3
const router = express.Router();
4
const { connections } = require('../models');
5
6
const sequelize = connections.default;
7
8
router.get('/customer_stats', (req, res, next) => {
9
const limit = parseInt(req.query.page.size) || 20;
10
const offset = (parseInt(req.query.page.number) - 1) * limit;
11
const queryType = sequelize.QueryTypes.SELECT;
12
let conditionSearch = '';
13
14
if (req.query.search) {
15
conditionSearch = `customers.email LIKE '%${req.query.search.replace(/\'/g, '\'\'')}%'`;
16
}
17
18
const queryData = `
19
SELECT customers.id,
20
customers.email,
21
count(orders.*) AS orders_count,
22
sum(products.price) AS total_amount,
23
customers.created_at,
24
customers.updated_at
25
FROM customers
26
JOIN orders ON customers.id = orders.customer_id
27
JOIN products ON orders.product_id = products.id
28
${conditionSearch ? `WHERE ${conditionSearch}` : ''}
29
GROUP BY customers.id
30
ORDER BY customers.id
31
LIMIT ${limit}
32
OFFSET ${offset}
33
`;
34
35
const queryCount = `
36
SELECT COUNT(*)
37
FROM customers
38
WHERE
39
EXISTS (
40
SELECT *
41
FROM orders
42
WHERE orders.customer_id = customers.id
43
)
44
${conditionSearch ? `AND ${conditionSearch}` : ''}
45
`;
46
47
Promise.all([
48
sequelize.query(queryData, { type: queryType }),
49
sequelize.query(queryCount, { type: queryType }),
50
])
51
.then(async ([customerStatsList, customerStatsCount]) => {
52
const customerStatsSerializer = new RecordSerializer({ name: 'customer_stats' });
53
const customerStats = await customerStatsSerializer.serialize(customerStatsList);
54
const count = customerStatsCount[0].count;
55
res.send({ ...customerStats, meta:{ count: count }});
56
})
57
.catch((err) => next(err));
58
});
59
60
module.exports = router;
Copied!
Work in progress - this section will soon be released
In the repository
lib/forest_liana/controllers/, we’ve created a controller file customer_stats.rb to implement API behind the Smart Collection.The logic here is to index 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
params[: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 JSON API Serializer library for this task.
lib/forest-liana/controllers/customer_stats_controller.rb
1
class Forest::CustomerStatsController < ForestLiana::ApplicationController
2
require 'jsonapi-serializers'
3
4
before_action :set_params, only: [:index]
5
6
class BaseSerializer
7
include JSONAPI::Serializer
8
9
def type
10
'customerStat'
11
end
12
13
def format_name(attribute_name)
14
attribute_name.to_s.underscore
15
end
16
17
def unformat_name(attribute_name)
18
attribute_name.to_s.dasherize
19
end
20
end
21
22
class CustomerStatSerializer < BaseSerializer
23
attribute :email
24
attribute :total_amount
25
attribute :orders_count
26
end
27
28
def index
29
customers_count = Customer.count_by_sql("
30
SELECT COUNT(*)
31
FROM customers
32
WHERE
33
EXISTS (
34
SELECT *
35
FROM orders
36
WHERE orders.customer_id = customers.id
37
)
38
AND email LIKE '%#{@search}%'
39
")
40
customer_stats = Customer.find_by_sql("
41
SELECT customers.id,
42
customers.email,
43
count(orders.*) AS orders_count,
44
sum(products.price) AS total_amount,
45
customers.created_at,
46
customers.updated_at
47
FROM customers
48
JOIN orders ON customers.id = orders.customer_id
49
JOIN products ON orders.product_id = products.id
50
WHERE email LIKE '%#{@search}%'
51
GROUP BY customers.id
52
ORDER BY customers.id
53
LIMIT #{@limit}
54
OFFSET #{@offset}
55
")
56
customer_stats_json = CustomerStatSerializer.serialize(customer_stats, is_collection: true, meta: {count: customers_count})
57
render json: customer_stats_json
58
end
59
60
private
61
62
def set_params
63
@limit = params[:page][:size].to_i
64
@offset = (params[:page][:number].to_i - 1) * @limit
65
@search = sanitize_sql_like(params[:search]? params[:search] : "")
66
end
67
68
def sanitize_sql_like(string, escape_character = "\\")
69
pattern = Regexp.union(escape_character, "%", "_")
70
string.gsub(pattern) { |x| [escape_character, x].join }
71
end
72
end
Copied!
You then need to create a route pointing to your collection's index action to get all your collection's records.
config/routes.rb
1
Rails.application.routes.draw do
2
# MUST be declared before the mount ForestLiana::Engine.
3
namespace :forest do
4
get '/CustomerStat' => 'customer_stats#index'
5
end
6
7
mount ForestLiana::Engine => '/forest'
8
9
end
Copied!
First we will add the right path to the urls.py file
app/urls.py
1
from django.urls import path
2
from django.views.decorators.csrf import csrf_exempt
3
4
from . import views
5
6
app_name = 'app'
7
urlpatterns = [
8
path('/CustomerStats', csrf_exempt(views.CustomerStatsView.as_view()), name='customer-stats'),
9
]
Copied!
Then we will create the pertained view
app/views.py
1
from django.http import JsonResponse
2
from django.views import generic
3
from django.db.models import Sum, Count
4
5
from django_forest.utils.schema.json_api_schema import JsonApiSchema
6
from django_forest.resources.utils.queryset.pagination import PaginationMixin
7
from django_forest.resources.utils.queryset.search import SearchMixin
8
9
class CustomerStatsViewView(PaginationMixin, SearchMixin, generic.ListView):
10
11
def get(self, request, *args, **kwargs):
12
params = request.GET.dict()
13
14
# queryset
15
queryset = Customer.objects.all()
16
17
# annotate
18
queryset = queryset.annotate(total_amount=Sum('product__prices'))
19
queryset = queryset.annotate(orders_count=Count('orders'))
20
21
# search
22
queryset = queryset.filter(self.get_search(params, Customer))
23
24
# pagination
25
queryset = self.get_pagination(params, queryset)
26
27
# use automatically generated Schema or use your own thanks to marshmallow-jsonapi
28
Schema = JsonApiSchema.get('CustomerStats')
29
data = Schema().dump(queryset, many=True)
30
31
return JsonResponse(data, safe=False)
Copied!
Create a controller
CustomerStatsControllerapp/Http/Controllers/CustomerStatsController.php
1
<?php
2
3
namespace App\Http\Controllers;
4
5
use App\Models\Customer;
6
use ForestAdmin\LaravelForestAdmin\Facades\JsonApi;
7
use Illuminate\Http\JsonResponse;
8
use Illuminate\Support\Facades\DB;
9
10
class CustomerStatsController extends Controller
11
{
12
/**
13
* @return JsonResponse
14
*/
15
public function index(): JsonResponse
16
{
17
$customerStats = Customer::select(DB::raw('customers.id, customers.email, COUNT(DISTINCT orders.*) AS orders_count, SUM(products.price) AS total_count'))
18
->join('orders', 'orders.customer_id', '=', 'customers.id')
19
->join('order_product', 'order_product.order_id', '=', 'orders.id')
20
->join('products', 'products.id', '=', 'order_product.product_id')
21
->groupBy('customers.id')
22
->orderBy('customers.id');
23
24
if (request()->has('search')) {
25
$customerStats->whereRaw("LOWER (customers.email) LIKE LOWER(?)", ['%' . request()->input('search') . '%']);
26
}
27
28
$pageParams = request()->query('page') ?? [];
29
30
return response()->json(
31
JsonApi::render(
32
$customerStats->paginate(
33
$pageParams['size'] ?? null,
34
'*',
35
'page',
36
$pageParams['number'] ?? null
37
),
38
'customerStat',
39
)
40
);
41
}
42
}
Copied!
Then add the route.
routes/web.php
1
<?php
2
3
use App\Http\Controllers\CustomerStatsController;
4
use Illuminate\Support\Facades\Route;
5
6
Route::get('forest/customerStat', [CustomerStatsController::class, 'index']);
Copied!
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.
Last modified 27d ago