Manage SQL views
Please be sure of your agent type and version and pick the right documentation accordingly.
This is the documentation of the forest-express-sequelize
and forest-express-mongoose
Node.js agents that will soon reach end-of-support.
forest-express-sequelize
v9 and forest-express-mongoose
v9 are replaced by @forestadmin/agent
v1.
Please check your agent type and version and read on or switch to the right documentation.
Manage SQL views
In SQL, a view is a virtual table based on the result-set of an SQL statement. Views can provide advantages over tables, such as:
represent a subset of the data contained in a table (see also segments).
join and simplify many tables into a single virtual table.
act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data.
Creating the SQL View
To create a view, we use CREATE VIEW
statement.
In the following example, we look for the user's email, the number of orders and the total amount spent.
CREATE VIEW customer_stats AS
SELECT customers.id,
customers.email,
count(orders.*) AS nb_orders,
sum(products.price) AS amount_spent,
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;
Adding the model
To display the SQL view on your Forest Admin interface, you must add the associated Sequelize model in your application.
'use strict';
module.exports = (sequelize, DataTypes) => {
const { Sequelize } = sequelize;
const CustomerStats = sequelize.define(
'customer_stats',
{
amount_spent: {
type: DataTypes.INTEGER,
},
nb_orders: {
type: DataTypes.STRING,
},
email: {
type: DataTypes.STRING,
},
},
{
tableName: 'customer_stats',
underscored: true,
schema: process.env.DATABASE_SCHEMA,
}
);
return CustomerStats;
};
Managing the view
Once your SQL view is implemented, you'll be able to filter, search, export and change the order of your fields.
Most of the time SQL views are used as read-only. If this is the case, we recommend changing the CRUD permission in your roles's settings.

Last updated
Was this helpful?