how can i use limit in include model using sequelize

Question

i want to get user's images at limit 2 from Follow model.

Models

const Follow = connector.define('Follow', {
    no: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    follower_id: {
        type: Sequelize.INTEGER,
        allowNull: true
    },
    target_id: {
        type: Sequelize.INTEGER,
        allowNull: true
    },
    isDelete: {
        type: Sequelize.BOOLEAN,
        allowNull: false
    },
    create_dt,
    delete_dt
}

const User = connector.define('User', {
    no: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    username: {
        type: Sequelize.STRING,
        allowNull: false

    },
    email: {
        type: Sequelize.STRING,
        allowNull: false
    },
    password: {
        type: Sequelize.STRING,
        allowNull: false
    },
    profile_img: {
        type: Sequelize.STRING,
        allowNull: true
    },
    bio: {
        type: Sequelize.STRING,
        allowNull: true
    },
    phone: {
        type: Sequelize.STRING,
        allowNull: true
    },
    gender: {
        type: Sequelize.STRING,
        allowNull: true
    },
    website: {
        type: Sequelize.STRING,
        allowNull: true
    },
    isDelete: {
        type: Sequelize.BOOLEAN,
        allowNull: false
    },
    create_dt,
    update_dt,
    delete_dt
}

const Image = connector.define('Image', {
    no: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    file: {
        type: Sequelize.STRING,
        allowNull: false
    },
    location: {
        type: Sequelize.STRING,
        allowNull: true
    },
    caption: {
        type: Sequelize.STRING,
        allowNull: true
    },
    tags: {
        type: Sequelize.STRING,
        allowNull: true
    },
    isDelete: {
        type: Sequelize.BOOLEAN,
        allowNull: false
    },
    create_dt,
    update_dt,
    delete_dt,
    user_id: {
        type: Sequelize.INTEGER,
        allowNull: true
    }
}

and, join

User.hasMany(Image, {foreignKey: 'user_id'})
Image.belongsTo(User, {foreignKey: 'user_id'})

User.hasMany(Follow, {foreignKey: 'follower_id'})
Follow.belongsTo(User, {foreignKey: 'follower_id'})

User.hasMany(Follow, {foreignKey: 'target_id'})
Follow.belongsTo(User, {foreignKey: 'target_id'})

so, i tried get user's images from follow by use include.

const followerImages = await Follow.findAll({
            attributes: ['target_id'],
            where:{
                follower_id: loginUser_id
            },
            include:[
                {
                    model: User,
                    required: true,
                    attributes: ['username', 'email', 'profile_img'],
                    include:[
                        {
                            model: Image,
                            required: true
                        }
                    ]
                }
            ]
        })

but i want to get images at limit 2.

so i tried that

const followerImages = await Follow.findAll({
            attributes: ['target_id'],
            where:{
                follower_id: loginUser_id
            },
            include:[
                {
                    model: User,
                    required: true,
                    attributes: ['username', 'email', 'profile_img'],
                    include:[
                        {
                            model: Image,
                            required: true,
                            limit: 2
                        }
                    ]
                }
            ]
        })

but it makes bugs i cant understand.

images field is a array contain empty object at 4.

all same..

what is the problem?

how can i solve this problem??

Eo

Have you tried putting limit at the very top of the query, like: Follow.findAll({limit: 2, attributes: ['target_id'], etc...}) – Attest

it's not my intent.. i want to get my 'every follower' s images at limit 2. it will load 2 followers's all images. – Mohammed

Answer 1

You can try :

include:[
    {
        model: Image,
        attributes : ['id','user_id','image'] , // <---- don't forget to add foreign key ( user_id )
        separate : true, // <--- Run separate query
        limit: 2
    }
]

Limit causes the issues some time on nested level , so it always safe to run that query separately.

Osteopathy

it's not working. but your advice is helpful to me. i will request 2 queries or slice result array after select except limit. it was my greed to solved by only one query. thank you – Mohammed

Thanks bro, You save my time <3 :) – Godfrey

can't apply separate: true if you running where clause – Fleischer

how can you order ASC now with limit in mind? – Lingua

Answer 2

Maybe you can apply this solution. I tried turning off duplicating and subQuery by adding a field with a false value in sequelize, like this:

duplicating: false, 
subQuery: false

for example as follows:

const followerImages = await Follow.findAll({
            attributes: ['target_id'],
            where:{
                follower_id: loginUser_id
            },
            include:[
                {
                    model: User,
                    required: true,
                    attributes: ['username', 'email', 'profile_img'],
                    include:[
                        {
                            model: Image,
                            required: true
                        }
                    ]
                }
            ],
            duplicating: false,
            subQuery: false
        })

Reference : https://github.com/sequelize/sequelize/issues/3007

Lafontaine