Tenancy - Global Level Records

I have tenancy configured, and I have a Document model which has a team_id, as standard really. However, I want some global Documents too, so I've made team_id nullable, on the basis that if it's not set it's a global level document available to all tenants.

Next, I want to show these global documents in the same table builder as the tenancy level documents, so I've added the following:

            ->modifyQueryUsing(function($query) {
                $query->orWhere('team_id', null);
            })

But the above code returns all null team_id records when filters are applied still.

I'm obviously not approaching this the right way so could use some advice how best to tackle this.

LaryAI

2 years ago

Level 58

To address the issue you're facing with the query that includes both tenant-specific and global documents, you need to ensure that the query correctly handles the context of the current tenant while also including documents that are marked as global (i.e., team_id is null). The problem with your current approach using orWhere('team_id', null) is that it indiscriminately adds all global documents to the result set, regardless of other conditions that might be applied to the query.

A more refined approach would be to modify the query such that it includes documents that either belong to the current tenant or are marked as global, but still respects other query filters. Here’s how you can achieve this:

Assuming you have a way to identify the current tenant's team_id, let's call it $currentTeamId. You should modify the query to use a more complex condition that groups the tenant-specific and global document conditions together.

Here's how you can structure your query modification:

->modifyQueryUsing(function($query) use ($currentTeamId) {
    $query->where(function($q) use ($currentTeamId) {
        $q->where('team_id', $currentTeamId)
          ->orWhereNull('team_id');
    });
})

This modification uses a closure to group the conditions for fetching documents. Inside the closure:

It first checks if the team_id matches the current tenant's ID ($currentTeamId).
It then uses orWhereNull('team_id') to include documents that are marked as global.

This approach ensures that the query will return:

Documents that are specific to the current tenant.
Global documents that are available to all tenants.

By grouping these conditions inside a nested where clause, it ensures that other parts of your query (e.g., additional filters or conditions) are applied correctly without being overridden by the orWhere condition.

This should resolve the issue of fetching the correct set of documents based on the tenant context while also including global documents in the result set.