Eloquent Global Scopes make it easy to customize your Model’s queries, however sometimes it is just not enough.
You may want to use similar solution for you DB::table(..) calls as well. While there’s no built-in feature for this, you can still do it with just a little more effort, and here’s how it’s done.
Imagine multi-tenant app where you want to add tenant context to each query (and let it be row-based context, ie. each table has tenant_id column that defines that context):
|
1 2 3 4 5 6 7 8 9 |
// Eloquent call with global scope applied Client::query()->toSql(); // "select * from clients where tenant_id = ?" // but DB::table('clients')->toSql(); // "select * from clients" |
Unfortunately there is no way to inject custom Builder class to the Connection object, because it’s simply instantiated here:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/** * Begin a fluent query against a database table. * * @param string $table * @return \Illuminate\Database\Query\Builder */ public function table($table) { $processor = $this->getPostProcessor(); $query = new Query\Builder($this, $this->getQueryGrammar(), $processor); return $query->from($table); } |
It is also not extendable like Eloquent\Builder (MacroableTrait).
That said, we need to use custom Connection class that will call our Builder in overriden table method:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
// We need separate classes for each driver, so use a trait: <?php namespace My\Space; use Illuminate\Database\MySqlConnection as BaseConnection; class MySqlConnection extends BaseConnection { use TenantConnectionTrait; } <?php namespace My\Space; trait TenantConnectionTrait { /** * Begin a fluent query against a database table. * * @param string $table * @return \Illuminate\Database\Query\Builder */ public function table($table) { $query = new Builder($this, $this->getQueryGrammar(), $this->getPostProcessor()); return $query->from($table); } } |
Next we can adjust the Builder as we need:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
<?php namespace My\Space; use Illuminate\Database\Query\Builder as BaseBuilder; class Builder extends BaseBuilder { // // quite a few methods skipped for brevity // /* |---------------------------------------------------------------- | Query Builder overrides |---------------------------------------------------------------- */ /** * Set the table which the query is targeting. * * @param string $table * @return $this */ public function from($table) { if ($this->from) { $this->updateTenantConstraint($table); } else { $this->applyTenantScope($table); } parent::from($table); return $this; } /** * Get a new instance of the query builder. * * @return static */ public function newQuery() { return new static($this->connection, $this->grammar, $this->processor); } } |
This is the minimum, that we need to override for READ operations. Of course there is much more, like handling joins, disabling scope and so on and so forth. Full code will be available on github soon, so stay tuned!
Anyway, there is still one thing missing here – the most important one: IoC binding. In order to use our custom solution, we need to bind the connection in the Container. This will be done in the service provider:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
<?php namespace My\Space; use Illuminate\Support\ServiceProvider; class TenantServiceProvider extends ServiceProvider { private $drivers = [ 'mysql' => 'MySql', 'pgsql' => 'Postgres', 'sqlite' => 'SQLite', 'sqlsrv' => 'SqlServer', ]; /** * Register the service provider. * * @return void */ public function register() { foreach ($this->drivers as $key => $driver) { $this->bind($key, $driver); } } /** * Bind custom connection in the IoC. * * @param string $key * @param string $driver * @return void */ protected function bind($key, $driver) { $this->app->singleton("db.connection.{$key}", function($app, $deps) use ($driver) { list($connection, $database, $prefix, $config) = $deps; $concrete = "{$driver}Connection"; return new $concrete($connection, $database, $prefix, $config); }); } } |
Complete solution will let you do for example this (no need for Eloquent Global Scopes on your model anymore):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
// Scope applied by default [1] > DB::table('users')->toSql(); // 'select * from `users` where `users`.`tenant_id` in (?, ?, ?)' // Scope disabled on demand [2] > DB::noTenantScope(function () { return DB::table('users')->toSql();}); // 'select * from `users`' // Joins handled properly [3] > User::join('accounts as a', 'a.id', '=', 'users.account_id')->toSql(); // 'select * from `users` inner join `accounts` as `a` on `a`.`id` = `users`.`account_id` where `users`.`tenant_id` in (?, ?, ?) and `accounts`.`tenant_id` in (?, ?, ?)' // Full support for table aliasing [4] > DB::table('users')->join('accounts as a', 'a.id', '=', 'u.account_id')->from('users as u')->toSql(); // 'select * from `users` as `u` inner join `accounts` as `a` on `a`.`id` = `u`.`account_id` where `u`.`tenant_id` in (?, ?, ?) and `a`.`tenant_id` in (?, ?, ?)' // and much MUCH more |
