Laravel-5 and Multitenancy database setup
I am starting to develop a SaaS application and I have created my database structure. I am planning to create a middleware file that handles the database connection for this request. Inside this intermediate file, I want to create a model that will always only select rows from any table that matches the current connection cust_id
(foreign key).
For example:
$Customers->where('cust_id', $cust_id)->first();
How can I do this without specifying a where('cust_id', $cust_id)
select in every statement?
source to share
You can easily achieve this by using Bright Global Query Scopes in your models. You can find out more about them here: http://laravel.com/docs/5.1/eloquent#query-scopes
First you need to define a Multitenant visibility class that will update all running queries and add a constraint to the cust_id field :
class MultitenantScope implements ScopeInterface
{
public function apply(Builder $builder, Model $model)
{
if (Auth::id()) {
$builder->whereCustId(Auth::id());
} else {
$model = $builder->getModel();
// apply a constraint that will never be true
// so that no records are fetched for unauthorized users
$builder->whereNull($model->getKeyName());
}
}
public function remove(Builder $builder, Model $model)
{
$query = $builder->getQuery();
$query->wheres = collect($query->wheres)->reject(function ($where) {
return ($where['column'] == 'cust_id');
})->values()->all();
}
}
Then you need a trait that you add to the models you need to filter:
trait MultitenantTrait
{
public static function bootMultitenantTrait()
{
static::addGlobalScope(new MultitenantScope());
}
public static function allTenants()
{
return (new static())->newQueryWithoutScope(new MultitenantScope());
}
}
The last part adds to your MultitenantTrait model :
class SomeModel extends Eloquent {
use MultitenantTrait;
}
Now, every time you execute any query using Eloquent model methods, the cust_id constraint will be applied to the query , and only the models belonging to the given cust_id will be available .
If for some reason you need access to all objects, you can use the allTenants () method to run the request without further restriction:
$allRows = SomeModel::allTenants()->get();
Please keep in mind that I have not tested this exact code, so let me know if you run into any problems and I will be more than happy to make it work for you :)
source to share