Top level where clauses for nested includes ignored when using findByPk

[s-devaney]

• Docs state that you can filter nested includes using a top-level where clause (using "$deeply.nested.field$" syntax)
• However, top level where clauses appear to be ignored when using findByPk (I can understand why, I am not challenging this)
• As a result the where clause is not applied in the query

Perhaps something needs to be added to the docs about this, or update the findByPk method to apply any nested where clause?

[wbourne0]

Can you add some code samples for this? I don't fully understand the issue. Te generated SQL vs what you expect would also be super handy (if logging isn't already on, you can add logging: console.log to the query options)

[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. 🙂

[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. 🙂

[AngelRivas0day]

I have the same problem.

This is the code that triggered the error for me:

let application = await Application.findByPk(applicationId, { where: { status: ['open','quoted'], seller_company_id: portal.company_id }, include: [{ model: Policyholder, },{ model: Rating, include: [ { model: RatingLV, where: { locale }, limit: 1, required: true, }, { model: Product, where: { active: 1, deleted: 0, }, include: { model: ProductLV, limit: 1, required: true, where: { locale }, }, }, ], }], });

And the MySQL query that it generates is the following:

SELECT <all the fields the query includes> WHERE Application.id = 646;

The top-level where conditions are completely ignored in the resulting query.

The only way I've figured out how to solve it is simple. Just change findByPk for findOne and include the ID in the where conditions.