Nested queries setting a filter does not convert the Subquery to a JOIN

[MrSwitch]

When requesting fields from another model, a subquery is typically used when there is no filter provided. If however the handler then defines the filter, the nested model join is not converted from a Subquery to a JOIN, causing SQL errors. Tables referenced within a subquery are not available outside of that subquery, which is why we need regular JOINs.

dare.options.models.nestedTable = {
   get(options) {
       options.filter = {is_deleted: 0}; // should have been a `join` not a `filter`
   }
};


const query = {
   // ..
  fields: ['field', {nestedTable: ['field']]
};

Will produce SQL

SELECT ..., (SELECT JSON_OBJECT('field', b.field) FROM nestedTable b WHERE a.id = b.a_id) as nestedTable
FROM ...
WHERE b.is_deleted = 0
-- Error: Unknown column 'b.is_deleted' in 'where clause'

Approach
Allow setting of the filter in nested fields, but convert the request into a JOIN if it is not already.

[5app-Machine]

🎉 This issue has been resolved in version 0.92.1 🎉

The release is available on:

• npm package (@next dist-tag)
• GitHub release

Your semantic-release bot 📦🚀