Query through all provided schemas

Question

I have kind of 'restaurant app' with multi tenant architecture. Each Tenant in short words have their Order table where I keep all data regarding orders which were made.

I also have a User/Client table which is in public schema, cause each Client can make order in different restaurants (tenants).

Problem: The problem which came in my implementation is that I'd like to display to the client all orders that he made.

Question: How can I query through all (or provided) Tenants to query such data?

Current CurrentTenantIdentifierResolver looks like:

    public String resolveCurrentTenantIdentifier() {
        return Optional
                .ofNullable(TenantContext.getTenantSchema())
                .orElse(PUBLIC_TENANT);
    }

Answer 1

Whenever a user visits a tenant (Restaurant), you will be storing the Tenant's (AKA Restaurants) id that the user is in now as part of UserTenants or some other table like this.

When a consolidated orders list is required, you can run a query in the above table to get the list of all tenants (Restaurants) that the user has been to and then for each of the tenant, setup the context for the tenant (the one that helps setup the schema in the datasource) and you should be able to pull the data.

You can run the queries in parallel across all the tenant schema and then concatenate them all to get the full list of orders.