Limit and offset in association with include in sequelize

user_266598 0
Viewed 12611

I have 2 models Country, City.

They are associated with each other. City Belongs to Country. Now what I want to do is get cities list within a country query along with limit and offset for pagination if possible.

If i do below it will list all cities within a country. What I need to do is be able to limit cities with limit and offset params. 

Country.findById(1, {
  include: [
    { 
      model: City,
      as: 'cities'
    }
  ]
});

Country Model

module.exports = (sequelize, DataTypes) => {
    let Country = sequelize.define('Country', {
        id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
        code: {type: DataTypes.STRING, allowNull: false, unique: true },
        name: DataTypes.STRING
    });
    Country.associate = (models) => {
        Country.hasMany(models.City, {as: 'cities'});
    };
    return Country;
}

City Model

module.exports = (sequelize, DataTypes) => {
    let City = sequelize.define('City', {
        id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
        name: {type: DataTypes.STRING, allowNull: false, unique: true},
    });

    City.associate = (models) => {
        City.belongsTo(models.Country, {as: 'country'});
    };

    return City;
}
sequelize.js
3 Answers
user_40492710

Here you go :

Country.findById(1, {
  include: [
    { 
        attributes : ["id", "countryId", "name"]     
        model: City,
        as: 'cities' ,
        separate: true,
        offset: 5, // <--- OFFSET
        limit: 5 // <--- LIMIT
    }
  ]
});

For more detail : DO READ

user_23494070

I'm going to post some kind of updated (and not so updated actually) answer.

The offset and limit work in nested includes just as they would work on top level.

I have tested them with 2 levels nested include, something like

User.findOne(
{
 where: {id: 1},
  include: 
    [ 
     {
      model: Tasks,
      include:
        [
         {
           model: Unfinished, limit: 3, offset: 10
         }
        ]
     }
    ]
});

Now the thing is that this is NOT documented (or at least I did not find any documentation) and if you are using Typescript it will give you error so you have to cast the options object to any, but it works as expected (at least for me)!

user_22042790
Related