Laravel Change Schema Dynamically

Viewed 436

I am working on a project in laravel 5.8 and postgres. I have one main database, a global schema with master data and other schemas for each organization. The schema with the master data is the default connection. I am attempting to search for a specific phone number in each schema and return all the schema names that a phone number appears in. My first attempt was this, but the schema does not switch from the default schema and the search does not occur.

My code looks like this :

public function authenticate(Request $request)
    {

        $phone_number = $request->get('phone_number');

        $facilities = Facility::all();

        foreach($facilities as $facility) {

            $params = $facility['schema_name'];

            $connection = DatabaseConnection::setConnection($params);

            $fac = Patient::where(function ($query) use ($phone_number) {
                $query->where('phone_number', '=', $phone_number);
            });

            $facility = $fac;

        }       

        return response()->json(compact('token', 'connection', 'facility'));
    }

I have a helper function, that looks like this :

public static function setConnection($params)
    {
        config(['database.connections.onthefly' => [
        
            'driver' => 'pgsql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'schema' => $params,
            'sslmode' => 'prefer',
        ]]);

        return DB::connection('onthefly');
    }

I have also tried to switch schemas using DB::statement("SET search_path TO $params"); before trying the helper function which did not work.

Any advice/references on how to switch the schema dynamically will be appreciated.

0 Answers
Related