Use LEFT JOIN instead of INNER JOIN when translate condition to SQL

[ccatterina]

Hi,
I run into a problem while trying to handle the following situation:

I want that all users can read their things:

can('read', 'Thing', { "owners.user_id": user.id })

But i want also to add a specific rule to permit to alice to read a Thing that she doesn't own (the thing with id 3), so I have an additional rule for alice that says:

{
  action: 'read',
  subject: 'Thing',
  conditions: { id: 3 }
}

This situation results in a SQL statement similar to:

SELECT ... FROM Thing INNER JOIN ThingsOwners ON Thing.id = ThingsOwners.thing_id WHERE ( Thing.id = 3 or ThingsOwners.user_id = ALICE_ID)

That works as expected until the thing 3 has at least an owner, otherwise the thing 3 is filtered out by the INNER JOIN.

Using LEFT JOIN should fix this issue and afaik it wouldn't introduce any other problem.

What do you think about it? If you are ok with it I can work on a PR to replace INNER JOIN with LEFT JOIN. Thanks!