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.

models/customer_stats.js
'use strict';
​
module.exports = (sequelize, DataTypes) => {
  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.