include with depth 2 and main where to include table

[sneznaovca]

I have table articles with columns id,id_author, id_editor. Second table is users with id and code. Models have relations on both articles columns (hasMany,belongsTo).

I need select all articles if users.code match constant or if both are null. I can't use where in include (or I don't know how for this conditions with OR).

I have something like..and it works correct

let options = {
            include: [
                {
                    model: Author,
                    as: 'Create_ref',
                    attributes: ['code'],
                    required: false,
                },
                {
                    model: Author,
                    as: 'Editor_ref',
                    attributes: ['code'],
                    required: false,
                }
            ],
            where: {
                [Op.and]: [
                    {
                        [Op.or]: [
                            Articles.sequelize.where(Articles.sequelize.col('Create_ref.code'), '=', constant),
                            Articles.sequelize.where(Articles.sequelize.col('Editor_ref.code'), '=', constant),
                             {
                                [Op.and]: [
                                    Articles.sequelize.where(Articles.sequelize.col('Create_ref.code'), '=', null),
                                    Articles.sequelize.where(Articles.sequelize.col('Editor_ref.code'), '=', null),
                                ]
                            }
                        ],
                    }
                    , // more conditions
                ]
            },
        };

but when I add new include with include sequelize generate SQL with subquery and where is inside subquery but joins are outside. Query fail on Unknown column 'Create_ref.code' in 'where clause'

Is it bug or I need different approach?

        let opt = {
            include: [
                {
                    model: Author,
                    as: 'Create_ref',
                    attributes: ['code'],
                    required: false,
                },
                {
                    model: Author,
                    as: 'Editor_ref',
                    attributes: ['code'],
                    required: false,
                },{
                    model: Services,
                    as: 'article_services_hasMany',
                    include: [
                        'service_ref'
                    ]
                },
            ],
            where: {
                [Op.and]: [
                    {
                        [Op.or]: [
                            Articles.sequelize.where(Articles.sequelize.col('Create_ref.code'), '=', constant),
                            Articles.sequelize.where(Articles.sequelize.col('Editor_ref.code'), '=', constant),
                            {
                                [Op.and]: [
                                    Articles.sequelize.where(Articles.sequelize.col('Create_ref.code'), '=', null),
                                    Articles.sequelize.where(Articles.sequelize.col('Editor_ref.code'), '=', null),
                                ]
                            }
                        ],
                    }
                    , // more conditions
                ]
            },
        };

Issue Template Checklist

Is this issue dialect-specific?

• No. This issue is relevant to Sequelize as a whole.
• Yes. This issue only applies to the following dialect(s): XXX, YYY, ZZZ
• I don't know.

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

I tried it also on 5.21.3 and 6.0.0-beta 4, same error. I used mysql2 engine.

I found a quick fix. Add separate: true, to last include and split query but it is not a proper solution.

[ralexandr]

Met same problem

[talal7860]

Looking for a solution as well

[sneznaovca]

I probably found a solution, add to include object subQuery: false,

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂