Sequelize limit include association

Question

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;

score 5 · Accepted Answer · answered Feb 04 '15 at 22:33

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;

I have the same problem as above and if I try the above solution it adds an order by clause both in the subquery and outside of it. Outside of the subquery fails with an error ofcourse 'unknown column ... in order clause'. — adrianvlupu, May 09 '16 at 14:46
Not sure why this is the accepted answer, even though there was indeed a problem with the 'order'. To avoid the subquery and keep LIMIT at the end, you either need to call findAll({ subQuery: false, ...}), or findAll({include: { duplicating: false, ...}). See also https://stackoverflow.com/questions/26021965/sequelize-with-nodejs-cant-join-tables-with-limit — sebastien.b, Dec 29 '16 at 05:48

score 0 · Answer 2 · answered Sep 01 '16 at 07:34

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){

})

Sequelize limit include association

2 Answers2

Linked