CODEWITHSUNDEEP
phpmysqllaravel-5.6
Edmund Sulzanok
Edmund Sulzanok

Reputation: 1973

How to query all multi tenant databases?

I'm using hyn/laravel-tenancy package, which generates separate table for each customer. In admin panel I want to be able to access data from all customer databases. Couldn't find a solution in official documentation and can't find a good practice for this, so came up with this:

    $users = DB::table('users'); //System connection

    foreach (Website::all() as $w) {

        // for each tenant
        // setup a temporary connection

        config([
            'database.connections.' . $w->uuid => [
                'driver' => 'mysql',
                'host' => env('DB_HOST', '127.0.0.1'),
                'port' => env('DB_PORT', '3306'),
                'database' => $w->uuid,
                'username' => env('DB_USERNAME', 'root'),
                'password' => env('DB_PASSWORD', ''),
                'unix_socket' => env('DB_SOCKET', ''),
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'strict' => true,
                'engine' => null,
            ]
        ]);

        $w_connection = DB::connection($w->uuid)->table('users');

        $users = $users->unionAll($w_connection);
    }

    return $users->get();

Now I only have one tenant table, one user in system table and one in tenant table. The as the result I get user form system table twice.

Upvotes: 1

Views: 2331

Answers (1)

Edmund Sulzanok
Edmund Sulzanok

Reputation: 1973

Ok, so appearantly you can't union queries from two different connections. But I found the solution in this post.

    $users = DB::table('tenancy2.users');

    foreach (Website::all() as $w) {
        $tenant_users = DB::table($w->uuid . '.users');
        $users = $users->union($tenant_users);
    }

    return $users->get();

Apparently, you can use connection to access DB not specified in that particular connection if you indicate DB before table.

Upvotes: 1

Related Questions