javascript - Sequelize limit include association - Stack Overflow

admin管理员组

文章数量:1303335

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

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;

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

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;

• javascript
• mysql
• sequelize.js

Share Follow asked Jan 27, 2015 at 15:50 Frank JensenFrank Jensen 36755 silver badges1414 bronze badges 0

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 5

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

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

The correct solution is:

order: ['updatedAt', 'DESC']

Complete working example:

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

Produces:

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;

• order field key model order: ['FieldOrder', 'DESC']

ex:

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

• order field include model order: [ModelInclude,'FieldOrder', 'DESC']

ex:

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

}, function(err){

})

本文标签： javascriptSequelize limit include associationStack Overflow