Sequelize where condition used for included model-CodePudding

I have two models associated -> Order belongs to Phase and I want to use where clause when selecting the orders which have the chosen phase.projectId.

models.Order.findAll( {
        attributes:[
            'id',
            'product_id',
            'phase_id',
            [models.Order.sequelize.fn('sum', models.Order.sequelize.col('invoiced')), 'invoiced_quantity'],
        ],
        include: [{model:models.Phase, as:'phase', required: true}],
        where:{'$phase.projectId$': chosenProject.id},
        group:[
            'product_id'
        ],
        raw: true
    })

but it creates an error Unknown column 'phase.projectId' in 'where clause'

Is it possible to use where clause for included models?

CodePudding user response：

You can use where clause in include.

models.Order.findAll( {
        attributes:[
            'id',
            'product_id',
            'phase_id',
            [models.Order.sequelize.fn('sum', models.Order.sequelize.col('invoiced')), 'invoiced_quantity'],
        ],
        include: [{model:models.Phase, as:'phase', where: {id: chosenProject.id}],
        group:[
            'product_id'
        ],
        raw: true
    })

And the where clause in include make inner join (w/o required: true option).