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.