GROUP BY Sequelize Query _node.js

1, SQL and sequelize in the group query

 1.1 grouping queries in sql

In SQL query, the wildcard GROUP BY name implements a grouped query.GROUP BY clauses are used in conjunction with aggregate functions to complete a grouped query, and in a SELECT query field, you must appear in a clause without using an aggregate function ORDER BY. After the query is grouped, the result set is grouped by one or more columns.

GROUP BY Grammar

SELECT column name, aggregate function (column name) from
table name
WHERE column name operator value
GROUP by column name 
[having conditional expression] [with ROLLUP]

In the above statement:

Aggregate functions-grouped queries are typically used with aggregate functions, and aggregate functions include:

• COUNT ()-Used to count the number of record bars
• SUM ()-The sum of the values used to calculate the field
• AVG ()-the average value used to calculate the values of a field
• Max-used to find the maximum value for a query field
• Mix-used to find the minimum value of a query field
  

GROUP BY Child name-The field used to specify the grouping

HAVING Child name-for filtering grouped results, the result of the conditional expression will be displayed

WITH ROLLUP Child name-Used to specify append a record to summarize previous data

1.2 Grouped queries in sequelize

Using aggregate functions

Sequelize provides aggregate functions that can be aggregated directly into a model query:

1. Aggregate (field, aggregatefunction, [options])-Query by specified aggregate function
2. sum (field, [options])-sum
3. Count (field, [options])-Count query results
4. Max (field, [options])-Query maximum
5. Min (field, [options])-Query min value
   

In these aggregate functions, you can options.attributes options.attributes specify group-related fields through, properties, and you can specify options. having the filter criteria, but not WITH ROLLUP the arguments of the clause directly.

For example,.sum()a user order amount with a query order quantity greater than 1:

Order.sum (' price ', {attributes:[' name '), group: ' Name ', Plain:false, having:[' COUNT (?) '? ', ' name ', 1]}. Then (function (Result) {
 console.log (result);
})

The resulting SQL statement is as follows:

SELECT ' name ', sum (' price ') as ' sum ' to ' orders ' as ' orders ' GROUP by name have ' COUNT (' name ') >1;

Using aggregate parameters

In addition to using aggregate functions directly, you can also findAll() specify aggregate query-related parameters to implement aggregate queries in other methods. Query, you can also options.attributes options.attributes specify group-related fields through, properties, and options.having specify filter conditions. Unlike the direct use of aggregate function queries, when you build an aggregate query by using parameters, you set the aggregate field in the parameter as an array or an object, and you need to pass options.attributes in thesequelize.fn()aggregate function by method.

For example,.findAll()a user order amount with a query order quantity 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 resulting SQL statement is as follows:

SELECT ' name ', sum (' price ') as ' sum ' to ' orders ' as ' orders ' GROUP by name have ' COUNT (' name ') >1;

2. Use of examples

Now order form, the data is as follows:

> select * from Orders;
+---------+-------------+--------+-----------+---------------------+
| orderId | ordernumber | price | name  | CreatedOn   |
+---------+-------------+--------+-----------+---------------------+
|  1 | 00001  | 128.00 | Zhangxiao | 2016-11-25 10:12:49  | | 2 | 00002  | 102.00 | Zhangxiao | 2016-11-25 10:12:49  | | 4 | 00004  | 99.00 | Wang Xiao Five | 2016-11-25 10:12:49 |
|  3 | 00003  | 199.00 | Zhao Xiao Liu | 2016-11-25 10:12:49 |
+---------+-------------+--------+-----------+---------------------+

2.1 Simple to use

Use a grouped query to count the total orders for each customer.

Using SQL statements, you can query as follows:

> select name, SUM (price) from orders GROUP by name;
+-----------+------------+
| name  | SUM (price) |
+-----------+------------+
| Zhangxiao |  230.00 |
| Wang Xiao Five |  99.00 |
| Zhao Xiao Liu |  199.00 |
+-----------+------------+

The sequelize can be implemented as follows:

Order.findall ({attributes:[' sum ', [Sequelize.fn (' Sum ', Sequelize.col (' name ')), ' Sum ']], group: ' Name ', raw:true}. Then (function (Result) {
 console.log (result);
})

2.2 Using the HAVING clause

The total order amount for the user who counted the order quantity greater than 1.

Using SQL statements, you can do this as follows:

> select name, SUM (price) from orders GROUP by name has count (1) >1;
+-----------+------------+
| name  | SUM (price) |
+-----------+------------+
| Zhangxiao |  230.00 |
| Zhao Xiao Liu |  199.00 |
+-----------+------------+

Instead, use Sequelize to 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 Using the WITH ROLLUP clause

WITH ROLLUPClause is a new feature of the MySQL 5.5+ for summarizing statistical results. However, Sequelize does not yet support this feature when it is published.

Add Sum statistic column:

> select name, SUM (price) from orders GROUP by name with ROLLUP;
+-----------+------------+
| name  | SUM (price) |
+-----------+------------+
| Zhangxiao |  230.00 |
| Wang Xiao Five |  99.00 |
| Zhao Xiao Liu |  199.00 |
| NULL  |  528.00 |
+-----------+------------+

2.4 Connection queries and groupings

In order to manage conveniently, we will keep different information in different tables. For example, we would put the order information in one table and save the customer information in another table. For two tables that have an association relationship, we use a connection query to find the associated data, and you can also use aggregate functions when making a connection query.

The order form is as follows:

> select * from Orders;
+---------+-------------+--------+------------+---------------------+
| orderId | ordernumber | 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 | Zhangxiao | 1 | 1986-01-22 08:00:00 | 2016-11-25 10:16:35 |
| 2 | Li Xiaoxi | 2 | 1987-11-12 08:00:00 | 2016-11-25 10:16:35 |
| 3 | Wang Xiao Five | 1 | 1988-03-08 08:00:00 | 2016-11-25 10:16:35 |
| 4 | Zhao Xiao Liu | 1 | 1989-08-11 08:00:00 | 2016-11-25 10:16:35 |
+----+-----------+-----+---------------------+---------------------+

Use connection queries and group queries to count the total orders for each customer.

Queries using SQL statements are as follows:

> select C.name, sum (o.price) as sum from customers as C INNER JOIN orders as O ' O.customerid =c.id GROUP by C.name;

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

Order.belongsto (Customer, {foreignkey: ' customerId '});

Connection queries and groupings:

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);
})

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.

Alibaba Cloud Hot Products
Elastic Compute Service (ECS)	Dedicated Host (DDH)	ApsaraDB RDS for MySQL (RDS)	ApsaraDB for PolarDB(PolarDB)	AnalyticDB for PostgreSQL (ADB for PG)
AnalyticDB for MySQL(ADB for MySQL)	Data Transmission Service (DTS)	Server Load Balancer (SLB)	Global Accelerator (GA)	Cloud Enterprise Network (CEN)
Object Storage Service (OSS)	Content Delivery Network (CDN)	Short Message Service (SMS)	Container Service for Kubernetes (ACK)	Data Lake Analytics (DLA)
ApsaraDB for Redis (Redis)	ApsaraDB for MongoDB (MongoDB)	NAT Gateway	VPN Gateway	Cloud Firewall
Anti-DDoS	Web Application Firewall (WAF)	Log Service	DataWorks	MaxCompute
Elastic MapReduce (EMR)	Elasticsearch