Data aggregation of tenants

[luceos]

A question often asked is how to query tenants or how to build joins between system and tenants.

We should document this. See

https://discordapp.com/channels/146267795754057729/294067877424660480/463729903443902490

Sometimes tenant database users need access to system tables; we should explain how to run a query after tenant db creation that allows granting those rights.

[bkintanar]

I'm just going to place this here for reference later. My use case for this is that I've placed the marital_statuses table in the system database. Since in my use case, this table rarely changes and this table is shared to all tenant databases.

What I did was I listened to the \Hyn\Tenancy\Events\Database\Created::class event and whenever that event fires, the below Listener will be triggered.

<?php

namespace App\Listeners;

use Hyn\Tenancy\Events\Database\Created;
use Illuminate\Support\Facades\DB;

class GrantReferences
{
    protected $tables = ['marital_statuses'];

    /**
     * Handle the event.
     *
     * @param Created $event
     *
     * @return void
     */
    public function handle(Created $event)
    {
        $config = $event->config;

        $database = config('database.connections.system.database');

        foreach ($this->tables as $table) {
            DB::statement("CREATE USER IF NOT EXISTS `{$config['username']}`@'{$config['host']}' IDENTIFIED BY '{$config['password']}'");
            DB::statement("GRANT REFERENCES ON `{$database}`.`{$table}` TO `{$config['username']}`@'{$config['host']}'");
        }
    }
}

This listener will get the list of $tables which resides in the system database, Tenancy will grant the tenant database user GRANT REFERENCES to those tables. You can add as many system tables to the array list.