Eloquent Model dynamic schema connection. Order of Middleware Issue (Multi-tenant, Postgres)

In web.php I've switched Postgres schemas in middleware as the subdomain type of HTTP request is made. This way:

Route::group(
    [
        'domain'     => '{tenant}.' . config('app.url'),
        'middleware' => 'select-schema'
    ],
    function () {
        $this->get('/', 'HomeController@index')->middleware('auth');
    }
);

In select-schema middleware, I do something like this. This works correctly. (don't worry)

DB::select('SET search_path TO ' . {tenant});

My main problem is that: I've different migrations for public schema and for any individual tenant. In individual tenant I have users table. As soon I'm logged in it pop up this error.

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "users" does not exist

When I inject this way in User Model. Suppose cham11ng is my subdomain/schema.

protected $table = 'cham11ng.users';

Its Solves but I want to do it dynamically. Is there any way out? or I am doing something wrong?*

After this post I just discovered the main issue;

$this->get('/', 'HomeController@index')->middleware('auth');

The model works well but middleware auth execute first before select-schema

How do I order? select-schema then auth

bjones2015

8 years ago

Level 11

I haven't dug into this enough yet to know if this will work but it could be worth a shot.

Could you override the getTable method in the model and have it return the correct table name.

For example could you have a middleware that sticks the domain in the session or get the domain off of the request and do something like:

public function getTable()
{
    return request()->get('domain');
    //or
    // return session('domain');    
}

Let me know if that works for you please (or if you find another solution). I am actually about to start a multi-tenant project so your exact problem is something I have been mulling over.

cham11ng

8 years ago

Level 2

Oh yeah, getTable() methods is relevant solution but not complete.

Sticking through request has long procedure as sub-domain/schema name is not same all the time. Sub-domain convention is in kebab-case and Postgres schema doesn't support kebab-case. So it needs manipulation in between.

I don't really agree to store it in session but If I do I've to override it in LoginController@method. Is that the right way to do that?

cham11ng

8 years ago

Level 2

But I just discovered the main issue;

$this->get('/', 'HomeController@index')->middleware('auth');

The model works well but middleware auth execute first before select-schema

How do I order? select-schema then auth

bjones2015

8 years ago

Level 11

https://laravel.com/docs/5.4/middleware

That'll explain the steps more concisely than I would.

After you define the middleware you can pass it in as part of the home controller middleware. The order you pass in determines the order it is ran, so you'll wind up with something like:

$this->get('/', 'HomeController@index')->middleware('getSchemaName', 'auth');

With 'getSchemaName' being whatever you call your middleware.

cham11ng

8 years ago

Best Answer

Level 2

I've found the solution, For this, there's something called $middlewarePriority in App\Kernel.

Adding this help me solve the problem.

/**
 * Responsible for prioritizing the middleware
 *
 * @var array
 */
protected $middlewarePriority = [
    \App\Http\Middleware\SwitchSchema::class,
];

I've got solution from this link.

https://github.com/laravel/framework/issues/19565

Please or to participate in this conversation.