Using group by in Sequelize to perform group aggregation query

1. Group query in SQL and Sequelize

1.1 Group query in SQL

In SQL query, group query is implemented through GROUP BY language name. The GROUP BY clause must be used in conjunction with an aggregate function to complete the group query. In the fields of the SELECT query, if an aggregate function is not used, it must appear in the ORDER BY clause. After grouping the query, the query result is a result set grouped by one or more columns.

GROUP BY syntax

SELECT 列名, 聚合函数(列名)
FROM 表名
WHERE 列名 operator value
GROUP BY 列名 
[HAVING 条件表达式] [WITH ROLLUP]

In the above statement:

Aggregation function - Grouping queries are usually used together with aggregate functions, which include:

COUNT()-used to count the number of records

SUM()-used to calculate the sum of the field values

AVG()-used to calculate the average value of the field

MAX-used to find the maximum value of the query field

MIX-used to find the minimum value of the query field

GROUP BY subname-used to specify the field for grouping

HAVING subname - used to filter grouping results, results that match the conditional expression will be displayed

WITH ROLLUP subname - used to specify to append a record, use To summarize the previous data

1.2 Group query in Sequelize

Use aggregate functions

Sequelize provides aggregate functions, which can directly perform aggregate queries on the model:

aggregate(field, aggregateFunction, [options])-Query through the specified aggregate function

sum(field, [options])-Sum

count(field, [options])-Statistics on the number of query results

max(field, [options])-Query the maximum value

min(field, [options])-Query the minimum value

Among the above aggregate functions, grouping-related fields can be specified through options.attributes and options.attributes attributes, and filtering conditions can be specified through options.having, but the parameters of the WITH ROLLUP clause are not directly specified.

For example, use .sum() to query the order amount of users whose order quantity is greater than 1:

Order.sum('price', {attributes:['name'], group:'name', plain:false, having:['COUNT(?)>?', 'name', 1]}).then(function(result){
 console.log(result);
})

The generated SQL statement is as follows:

SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

Use aggregate parameters

In addition to using aggregate functions directly, you can also specify aggregation query-related parameters in methods such as findAll() to implement aggregation query. When querying, you can also specify grouping related fields through options.attributes and options.attributes attributes, and you can specify filter conditions through options.having. Unlike directly using aggregate function queries, when building an aggregate query through parameters, the aggregate fields in the options.attributes parameter must be set in the form of an array or object, and the aggregate function needs to be passed in through the sequelize.fn() method.

For example, use .findAll() to query the order amount of users whose order quantity is greater than 1:

Order.findAll({attributes:['name', [sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){
 console.log(result);
})

The generated SQL statement is as follows:

SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;

2. Usage examples

Now the order table, the data is as follows:

> select * from orders;
+---------+-------------+--------+-----------+---------------------+
| orderId | orderNumber | price | name  | createdOn   |
+---------+-------------+--------+-----------+---------------------+
|  1 | 00001  | 128.00 | 张小三 | 2016-11-25 10:12:49 |
|  2 | 00002  | 102.00 | 张小三 | 2016-11-25 10:12:49 |
|  4 | 00004  | 99.00 | 王小五 | 2016-11-25 10:12:49 |
|  3 | 00003  | 199.00 | 赵小六 | 2016-11-25 10:12:49 |
+---------+-------------+--------+-----------+---------------------+

2.1 Simple use

Use group query to count each The total order amount for each customer.

Using SQL statements, you can query like the following:

> select name, SUM(price) from orders GROUP BY name;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 王小五 |  99.00 |
| 赵小六 |  199.00 |
+-----------+------------+

And in Sequelize, you can implement it like the following:

Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', raw:true}).then(function(result){
 console.log(result);
})

2.2 Use the HAVING clause

to count the total order amount of users whose order quantity is greater than 1.

Using SQL statements, it can be implemented as follows:

> select name, SUM(price) from orders GROUP BY name HAVING count(1)>1;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 赵小六 |  199.00 |
+-----------+------------+

And using Sequelize, you can query as follows:

Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){
 console.log(result);
})

2.3 Use the WITH ROLLUP clause

The WITH ROLLUP clause is a new feature of MySQL 5.5+ and is used to summarize statistical results. However, at the time of publishing this article, Sequelize does not yet support this feature.

Add total statistics column:

> select name, SUM(price) from orders GROUP BY name WITH ROLLUP;
+-----------+------------+
| name  | SUM(price) |
+-----------+------------+
| 张小三 |  230.00 |
| 王小五 |  99.00 |
| 赵小六 |  199.00 |
| NULL  |  528.00 |
+-----------+------------+

2.4 Connection query and grouping

For the convenience of management, we will Save different information in different tables. For example, we would put order information in one table and customer information in another table. For two tables that are related, we will use join queries to find related data. When performing join queries, we can also use aggregate functions.

The order table is as follows:

> select * from orders;
+---------+-------------+--------+------------+---------------------+
| orderId | orderNumber | price | customerId | createdOn   |
+---------+-------------+--------+------------+---------------------+
|  1 | 00001  | 128.00 |   1 | 2016-11-25 10:12:49 |
|  2 | 00002  | 102.00 |   1 | 2016-11-25 10:12:49 |
|  3 | 00003  | 199.00 |   4 | 2016-11-25 10:12:49 |
|  4 | 00004  | 99.00 |   3 | 2016-11-25 10:12:49 |
+---------+-------------+--------+------------+---------------------+

The customer table structure is as follows:

> select * from customers;
+----+-----------+-----+---------------------+---------------------+
| id | name  | sex | birthday   | createdOn   |
+----+-----------+-----+---------------------+---------------------+
| 1 | 张小三 | 1 | 1986-01-22 08:00:00 | 2016-11-25 10:16:35 |
| 2 | 李小四 | 2 | 1987-11-12 08:00:00 | 2016-11-25 10:16:35 |
| 3 | 王小五 | 1 | 1988-03-08 08:00:00 | 2016-11-25 10:16:35 |
| 4 | 赵小六 | 1 | 1989-08-11 08:00:00 | 2016-11-25 10:16:35 |
+----+-----------+-----+---------------------+---------------------+

Use join query and group query to count the total order amount of each customer.

Use the SQL statement to query as follows:

> select c.name, SUM(o.price) AS sum from customers AS c INNER JOIN orders AS o ON o.customerId =c.id GROUP BY c.name;

When performing connection query in Sequelize, you first need to establish the association between models:

Order.belongsTo(Customer, {foreignKey: 'customerId'});

##Connection query and grouping:

var include = [{
 model: Customer,
 required: true,
 attributes: ['name'],
}]
Order.findAll({include:include, attributes:[[sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'Customer.name', having:['COUNT(?)>?', 'name', 1], raw:true, rollup:true}).then(function(result){
 console.log(result);
})

Summary

That’s it This is the entire content of this article. I hope the content of this article can be of some help to everyone’s study or work. If you have any questions, you can leave a message to communicate.

For more articles related to using group by to perform group aggregation query in Sequelize, please pay attention to the PHP Chinese website!