Sequelize ORM 通过分组获取最大 id [英] Sequelize ORM get max id by grouping

问题描述

我在 nodejs 项目中使用 sequelize ORM 进行数据库操作.在这里，我编写了一个简单的 sequelize 查询来检索驱动程序定位模型中每个驱动程序的最大 ID.

 global.driverlocationhistory.findAll({属性:[global._sequelize.fn("max", global._sequelize.col('id'))],组:[driverId"]}).then(函数(结果){objDrvr = 结果；})

这不会返回任何值，也不会给出任何类型的错误.但是数据库有值.当执行上面的 sequelize 查询时，它是创建的 SQL 查询.(使用 node server.js 运行服务器后可以在控制台窗口中看到)这里是执行查询.

SELECT max(`id`) FROM `driverlocationhistory` AS `driverlocationhistory` GROUP BY `driverId`;

上面的SQL-query是在mysql服务器中执行的，有查询结果.

为什么不为 sequelize 查询检索数据?

解决方案

可以试试吗，

await DIR.ProfileModel.findAll({属性: [[sequelize.fn('MAX', sequelize.col('profile_id')), 'profile_id'],],组:['user_id']，原始:真实，});

续集生成:

<块引用>

正在执行(默认):SELECT MAX(profile_id) FROM profile ASprofile GROUP BY user_id;

并返回结果:

{ profile_id: 106 },{ profile_id: 187 },{ profile_id: 109 },{ profile_id: 111 },{ profile_id: 112 },{ profile_id: 113 },{ profile_id: 115 },{ profile_id: 116 },{ profile_id: 117 },{ profile_id: 120 },{ profile_id: 121 },

如果raw为false，dataValues为空，我不知道为什么

<代码>{轮廓 {数据值:{}，_previousDataValues:{}，_改变:{}，_modelOptions:{时间戳:假，证实: {}，冻结表名称:假，下划线:假，underscoredAll:假，偏执:假的，拒绝空:假，whereCollection:空，架构:空，架构分隔符:'',默认范围:{}，范围:[]，索引:[]，名称:[对象]，omitNull: 假,字符集:'utf8'，整理:'utf8_general_ci'，表名:'个人资料'，续集:[对象]，钩子:[对象]，唯一键:{}},}}

<块引用>

有时您可能会期待一个您想要的海量数据集显示，无需操作.对于您选择的每一行，Sequelize创建一个具有更新、删除、获取功能的实例关联等.如果您有数千行，这可能需要一些时间时间.如果您只需要原始数据并且不想更新任何内容，你可以这样做来获取原始数据.

阅读文档

祝你好运！

I'm using sequelize ORM for DB operation in my nodejs project. Here I wrote a simple sequelize query to retrieve max ids of each driver in a driverlocation model.

 global.driverlocationhistory.findAll({
        attributes: [global._sequelize.fn("max", global._sequelize.col('id'))],
        group: ["driverId"]
 }).then(function (results) {
        objDrvr = results;
 })

This is not returning any value as well as not giving any kind of error. But the database has values. When executing above sequelize query, it is created SQL query. (It can be seen in console window after running the server using node server.js) Here is the executing query.

SELECT max(`id`) FROM `driverlocationhistory` AS `driverlocationhistory` GROUP BY `driverId`;

SQL-query above is executed in mysql server and there are results for the query.

Why is it not retrieving data for sequelize query?

解决方案

Can you please try,

await DIR.ProfileModel.findAll({
    attributes: [
        [sequelize.fn('MAX', sequelize.col('profile_id')), 'profile_id'],
    ],
    group: ['user_id'],
    raw: true,
});

Sequelize generate:

Executing (default): SELECT MAX(profile_id) FROM profile AS profile GROUP BY user_id;

And return results:

{ profile_id: 106 },
{ profile_id: 187 },
{ profile_id: 109 },
{ profile_id: 111 },
{ profile_id: 112 },
{ profile_id: 113 },
{ profile_id: 115 },
{ profile_id: 116 },
{ profile_id: 117 },
{ profile_id: 120 },
{ profile_id: 121 },

If raw is false, dataValues is empty, I have no idea why

{
    profile {
        dataValues: {},
        _previousDataValues: {},
        _changed: {},
        _modelOptions:
        {
            timestamps: false,
            validate: {},
            freezeTableName: false,
            underscored: false,
            underscoredAll: false,
            paranoid: false,
            rejectOnEmpty: false,
            whereCollection: null,
            schema: null,
            schemaDelimiter: '',
            defaultScope: {},
            scopes: [],
            indexes: [],
            name: [Object],
            omitNull: false,
            charset: 'utf8',
            collate: 'utf8_general_ci',
            tableName: 'profile',
            sequelize: [Object],
            hooks: [Object],
            uniqueKeys: {}
        },
    }
}

Sometimes you might be expecting a massive dataset that you just want to display, without manipulation. For each row you select, Sequelize creates an instance with functions for update, delete, get associations etc. If you have thousands of rows, this might take some time. If you only need the raw data and don't want to update anything, you can do like this to get the raw data.

Read docs

Good luck!

这篇关于Sequelize ORM 通过分组获取最大 id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！