Sequelize 子查询

考虑你有两个模型,即 Post 和 Reaction，它们之间建立了一对多的关系（不使用外键级联），因此一个 post 有很多 reactions：

subquery/create.js

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('runoops_test', 'root', '123456', {
    host: 'localhost',
    dialect: 'mysql', /* 选择 'mysql' | 'mariadb' | 'postgres' | 'mssql' 其一 */
    freezeTableName: true
});

const Post = sequelize.define('post', {
    content: DataTypes.STRING
}, { timestamps: false });

const Reaction = sequelize.define('reaction', {
    type: DataTypes.STRING
}, { timestamps: false });

Post.hasMany(Reaction,{
    constraints: false, //不使用级联关系
  });
Reaction.belongsTo(Post,{
    constraints: false, //不使用级联关系
  });

async function makePostWithReactions(content, reactionTypes) {
    const post = await Post.create({ content });
    await Reaction.bulkCreate(
        reactionTypes.map(type => ({ type, postId: post.id }))
    );
    return post;
}

//添加数据
(async () => {
    await Post.sync();
    await Reaction.sync();
    await makePostWithReactions('Hello World', [
        'Like', 'Angry', 'Laugh', 'Like', 'Like', 'Angry', 'Sad', 'Like'
    ]);
    await makePostWithReactions('My Second Post', [
        'Laugh', 'Laugh', 'Like', 'Laugh'
    ]);

})();

现在，我们已经准备好子查询功能的示例。

假设我们要通过 SQL 为每个帖子计算一个 laughReactionsCount。我们可以通过子查询来实现，例如：

SELECT
    *,
    (
        SELECT COUNT(*)
        FROM reactions AS reaction
        WHERE
            reaction.postId = post.id
            AND
            reaction.type = "Laugh"
    ) AS laughReactionsCount
FROM posts AS post

结果如图：

Sequelize 通过将 finder 方法(例如,findAll)的 attributes 参数与 sequelize.literal 函数功能结合使用：

subquery/query.js

...
//查询数据
(async () => {
    const post = await Post.findAll({
        attributes: {
            include: [
                [
                    // 注意下面的调用中的括号！
                    sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM reactions AS reaction
                    WHERE
                        reaction.postId = post.id
                        AND
                        reaction.type = "Laugh"
                )`),
                    'laughReactionsCount'
                ]
            ]
        }
    });
    const postMap = post.map(item => item.toJSON());
    console.log(postMap);
})();

结果：

$ node subquery/query.js

...
[
  { id: 1, content: 'Hello World', laughReactionsCount: 1 },
  { id: 2, content: 'My Second Post', laughReactionsCount: 3 }
]

使用子查询进行复杂排序

//查询数据
(async () => {
   
    //查询并排序
    const post = await Post.findAll({
        attributes: {
            include: [
                [
                    // 注意下面的调用中的括号！
                    sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM reactions AS reaction
                    WHERE
                        reaction.postId = post.id
                        AND
                        reaction.type = "Laugh"
                )`),
                    'laughReactionsCount'
                ]
            ]
        },
        order: [
            [sequelize.literal('laughReactionsCount'), 'DESC']
        ]
    });
    const postMap = post.map(item => item.toJSON());
    console.log(postMap);
    
})();

结果：

[
  { id: 2, content: 'My Second Post', laughReactionsCount: 3 },
  { id: 1, content: 'Hello World', laughReactionsCount: 1 }
]

exists 查询

(async () => {

    //添加一条未关联的数据
    await Reaction.create({type:'Runoops',postId:3});

    //查询Post在Reaction表中存在的数据
    const reaction = await Reaction.findAll({
        where: {
            [Sequelize.Op.and]: [
                
                    // 注意下面的调用中的括号！
                    sequelize.literal(`exists (
                    SELECT 1 
                    FROM posts AS post
                    WHERE
                        reaction.postId = post.id
                )`)

            ]
        }
    });
    const reactionMap = reaction.map(item => item.toJSON());
    console.log(reactionMap);


})();

exist 查询结果：

$ node subquery/queryexists.js
Executing (default): SELECT `id`, `type`, `postId` FROM `reactions` AS `reaction` WHERE (exists (
                    SELECT 1
                    FROM posts AS post
                    WHERE
                        reaction.postId = post.id
                ));
[
  { id: 1, type: 'Like', postId: 1 },
  { id: 2, type: 'Angry', postId: 1 },
  { id: 3, type: 'Laugh', postId: 1 },
  { id: 4, type: 'Like', postId: 1 },
  { id: 5, type: 'Like', postId: 1 },
  { id: 6, type: 'Angry', postId: 1 },
  { id: 7, type: 'Sad', postId: 1 },
  { id: 8, type: 'Like', postId: 1 },
  { id: 9, type: 'Laugh', postId: 2 },
  { id: 10, type: 'Laugh', postId: 2 },
  { id: 11, type: 'Like', postId: 2 },
  { id: 12, type: 'Laugh', postId: 2 }
]

本教程实例代码：https://gitee.com/liqiang88/sequelize-test