续集限制包括关联 [英] Sequelize limit include association

问题描述

我在限制结果和包含相关模型时遇到Sequelize的问题。

I have a problem with Sequelize when limiting results and including associated models.

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

The following produces the correct result, limited by 10 and sorted correctly.

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;

然而，当我添加一个关联时，它突然限制了子查询，因此订单永远不会发生因为有限的结果集。

However when I add an association it suddently limits on the subquery instead and thus the ordering never happens because of a limited result set.

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;

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

What I'm was expecting was having the limit on the top query as so:

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

推荐答案

你不应该同时使用钥匙和方向在您订单的单个字符串中。来自 docs ：

You shouldn't use both the key and the direction in a single string on your order. From the docs:

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

'username DESC', // will return username DESC -- i.e. don't do it!

正确的解决方案是：

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;

这篇关于续集限制包括关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！