续集限制包括关联-Java 学习之路

在限制结果和包括相关模型时，我遇到了Sequelize的问题 .

以下产生正确的结果，限制为10并正确排序 .

Visit.findAll({
  limit: 10,
  order: 'updatedAt DESC',
}).success(function(visits) {
  res.jsonp(visits);
}).failure(function(err) {
  res.jsonp(err);
})

SQL

SELECT * FROM `Visits` ORDER BY updatedAt DESC LIMIT 10;

但是当我添加一个关联时，它突然限制了子查询，因此由于结果集有限，因此排序永远不会发生 .

Visit.findAll({
  limit: 10,
  order: 'updatedAt DESC',
  include: [
    { model: Account, required: true }
  ]
}).success(function(visits) {
  res.jsonp(visits);
}).failure(function(err) {
  res.jsonp(err);
})

SQL

SELECT 
  `Visits`.* 
FROM 
  (SELECT 
    `Visits`.*, `Account`.`id` AS `Account.id`, `Account`.`email` AS `Account.email`, `Account`.`password` AS `Account.password`, `Account`.`role` AS `Account.role`, `Account`.`active` AS `Account.active`, `Account`.`createdAt` AS `Account.createdAt`, `Account`.`updatedAt` AS `Account.updatedAt`, `Account`.`practice_id` AS `Account.practice_id` 
  FROM 
    `Visits` INNER JOIN `Accounts` AS `Account` ON `Account`.`id` = `visits`.`account_id` LIMIT 10) AS `visits` 
ORDER BY updatedAt DESC;

我期待的是对顶级查询的限制如下：

SELECT 
  ...
FROM 
  (SELECT ...) AS `Visits`
ORDER BY `Visits`.updatedAt DESC LIMIT 10
LIMIT 10;

2 回答

您不应在订单的单个字符串中同时使用键和方向 . 来自docs：

'用户名DESC'，//将返回用户名DESC - 即不要这样做！

正确的解决方案是：

order: ['updatedAt', 'DESC']

完整的工作示例：

'use strict';

var Sequelize = require('sequelize');
var sequelize = new Sequelize(
    'test', // database
    'test', // username
    'test', // password
    {
        host: 'localhost',
        dialect: 'postgres'
    }
);

var Customer = sequelize.define('Customer', {
    firstName: {type: Sequelize.STRING},
    lastName: {type: Sequelize.STRING}
});

var Order = sequelize.define('Order', {
    amount: {type: Sequelize.FLOAT}
});

var firstCustomer;

Customer.hasMany(Order, {constraints: true});
Order.belongsTo(Customer, {constraints: true});

sequelize.sync({force: true})
    .then(function () {
        return Customer.create({firstName: 'Test', lastName: 'Testerson'});
    })
    .then(function (author1) {
        firstCustomer = author1;
        return Order.create({CustomerId: firstCustomer.id, amount: 10});
    })
    .then(function () {
        return Order.create({CustomerId: firstCustomer.id, amount: 20})
    })
    .then(function () {
        return Order.findAll({
            limit: 10,
            include: [Customer],
            order: [
                ['updatedAt', 'DESC']
            ]
        });
    })
    .then(function displayResults(results) {
        results.forEach(function (c) {
            console.dir(c.toJSON());
        });
    })
    .then(function () {
        process.exit(0);
    });

生产环境：

SELECT "Order"."id", "Order"."amount", "Order"."createdAt", "Order"."updatedAt", "Order"."CustomerId", "Customer"."id" AS "Customer.id", "Customer"."firstName" AS "Customer.firstName", "Customer"."lastName" AS "Customer.lastName", "Customer"."createdAt" AS "Customer.createdAt", "Customer"."updatedAt" AS "Customer.updatedAt" FROM "Orders" AS "Order" LEFT OUTER JOIN "Customers" AS "Customer" ON "Order"."CustomerId" = "Customer"."id" ORDER BY "Order"."updatedAt" DESC LIMIT 10;

回复于 2024-05-03T03:35:33+08:00

订单字段键模型 order: ['FieldOrder', 'DESC']

例如：

db.ModelA.findAll({
    include: [{
        model: db.ModelB
    }],
    order: ['CreatedDateModelA', 'DESC']
})
.then(function(response){
}, function(err){
})

订单字段包含型号 order: [ModelInclude,'FieldOrder', 'DESC']

例如：

db.ModelA.findAll({
    include: [{
        model: db.ModelB
    }],
    order: [db.ModelB,'CreatedDateModelA', 'DESC']
})
.then(function(response){

}, function(err){

})

回复于 2024-05-03T03:35:33+08:00

续集限制包括关联

2 回答

相关问题