feat(core): auto-join M:1 and 1:1 relations with filters

[B4nan]

Filters are applied to the relations too, as part of JOIN ON condition. If a filter exists on a M:1 or 1:1 relation target, such an entity will be automatically joined, and when the foreign key is defined as NOT NULL, it will result in an INNER JOIN rather than LEFT JOIN. This is especially important for implementing soft deletes via filters, as the foreign key might point to a soft-deleted entity. When this happens, the automatic INNER JOIN will result in such a record not being returned at all.

You can disable this behavior via autoJoinRefsForFilters ORM option.

Related: #4975

[codecov]

Codecov Report

Attention: 1 lines in your changes are missing coverage. Please review.

Comparison is base (8b57319) 99.78% compared to head (cf4df54) 99.78%.
Report is 1 commits behind head on master.

❗ Current head cf4df54 differs from pull request most recent head ff03f43. Consider uploading reports for the commit ff03f43 to get more accurate results

Files	Patch %	Lines
packages/knex/src/query/QueryBuilder.ts	91.66%	1 Missing ⚠️

Additional details and impacted files

@@           Coverage Diff           @@
##           master    #5063   +/-   ##
=======================================
  Coverage   99.78%   99.78%           
=======================================
  Files         221      221           
  Lines       16351    16384   +33     
  Branches     3936     3954   +18     
=======================================
+ Hits        16316    16349   +33     
  Misses         35       35           

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.