Outer Join

[dragos-rosca]

Found a new oversite on nodebb/packages/knex-filters/src/queryBuilder.js .
If I build a query with any kind of "outer join" the library will automatically add a "where TenantId" clause. But the purpose of "outer join" is to show data from a table even if the joined table returns null. If the join table shows null on all columns that means t.TenantId will also be null and our forced where clause will remove the row.

There is a quick fix that might work, to just put where [...] and (tenantId is null or tenantId='value') [...]. Or we can go with a more sophisticated fix that will notice the outer joins and put the tenant restriction in the on space like: [...] from A left join B on A.id = B.idOfA and B.tenantId = 'value' [...] this will bring in the correct data. But you have left, right and full outer join and all cases must be covered.

[oncicaradupopovici]

This is not a bug of this library Knex-filters. The library does not expose tenancy filters, and does not add where clauses in your querries. This library only gives you some hooks you can plug-in in the QueryBuilder so you can inject your custom clauses.

[oncicaradupopovici]

The new version of knex-filters allows for a more fine-grained control about the statement selecting from the targeted table: from, left join, cross-join, etc....

const filter = createFilter(tableHasColumnTenantId, {
onSelect: {
from: addWhereTenantIdClause,
innerJoin: addWhereTenantIdClause,
leftJoin: addOnTenantIdClause,
},
onUpdate: addWhereTenantIdClause,
onDelete: addWhereTenantIdClause,
onInsert: (_table, _alias, _queryBuilder, inserted) => {
inserted[columnTenantId] = tenantId
},
})

See #21