Laravel 模型实现多库查询或者多表映射 · php开发笔记

### 场景说明在 laravel 开发过程中，在项目到达一定的程度，经常要做分库或者分表进行项目的调整，这里，来说一下遇到这样的问题之后怎么进行多库连接和多表映射 ### Elequent 查询原理平时我们进行基础查询: ~~~php $user = User::find(1); $article = Article::where('author_id',5)->first(); ~~~ Elequent 是如何进行操作的呢？先看基础 Model 的实现 ~~~php <?php namespace Illuminate\Database\Eloquent; ... abstract class Model implements ArrayAccess, Arrayable, Jsonable, JsonSerializable, QueueableEntity, UrlRoutable { ... /** * The connection name for the model. * * @var string */ protected $connection; /** * The table associated with the model. * * @var string */ protected $table; /** * The primary key for the model. * * @var string */ protected $primaryKey = 'id'; ... /** * Begin querying the model. * * @return \Illuminate\Database\Eloquent\Builder */ public static function query() { return (new static)->newQuery(); } /** * Get a new query builder for the model's table. * * @return \Illuminate\Database\Eloquent\Builder */ public function newQuery() { return $this->registerGlobalScopes($this->newQueryWithoutScopes()); } /** * Handle dynamic method calls into the model. * * @param string $method * @param array $parameters * @return mixed */ public function __call($method, $parameters) { if (in_array($method, ['increment', 'decrement'])) { return $this->$method(...$parameters); } return $this->newQuery()->$method(...$parameters); } /** * Handle dynamic static method calls into the method. * * @param string $method * @param array $parameters * @return mixed */ public static function __callStatic($method, $parameters) { return (new static)->$method(...$parameters); } /** * Convert the model to its string representation. * * @return string */ public function __toString() { return $this->toJson(); } } ~~~ > laravel 通过 protected $table; 进行表连接，通过 protected $connection; 选择连接的库，具体请参考[数据表名称](https://learnku.com/docs/laravel/5.6/eloquent#be7dce) 从上面看出，我们通过查询时，无论是静态方法还是实体方法，都无一例外调用到了 newQuery 进行查询操作. 所以一个很方便的方法就是重写 Model 的`query`方法 ### 配置多库 config/database.php 配置 ~~~php 'connections' => [ 'sqlite' => [ 'driver' => 'sqlite', 'database' => env('DB_DATABASE', database_path('database.sqlite')), 'prefix' => '', ], 'com_db' => [ 'driver' => 'mysql', 'host' => env('DB_COM_HOST', '127.0.0.1'), 'port' => env('DB_COM_PORT', '3306'), 'database' => env('DB_COM_DATABASE', 'forge'), 'username' => env('DB_COM_USERNAME', 'forge'), 'password' => env('DB_COM_PASSWORD', ''), 'unix_socket' => env('DB_COM_SOCKET', ''), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'strict' => false, 'engine' => null, ], 'couk_db' => [ 'driver' => 'mysql', 'host' => env('DB_CO_UK_HOST', '127.0.0.1'), 'port' => env('DB_CO_UK_PORT', '3306'), 'database' => env('DB_CO_UK_DATABASE', 'forge'), 'username' => env('DB_CO_UK_USERNAME', 'forge'), 'password' => env('DB_CO_UK_PASSWORD', ''), 'unix_socket' => env('DB_CO_UK_SOCKET', ''), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'strict' => false, 'engine' => null, ], 'de_db' => [ 'driver' => 'mysql', 'host' => env('DB_DE_HOST', '127.0.0.1'), 'port' => env('DB_DE_PORT', '3306'), 'database' => env('DB_DE_DATABASE', 'forge'), 'username' => env('DB_DE_USERNAME', 'forge'), 'password' => env('DB_DE_PASSWORD', ''), 'unix_socket' => env('DB_DE_SOCKET', ''), 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', 'strict' => false, 'engine' => null, ], ] ~~~ 现在，如果我需要根据不同的国家映射到不同的数据库，我就可以重建一个抽象的 Model, 重写 Region ~~~php <?php namespace App\Models; use Illuminate\Database\Eloquent\Model; abstract class Region extends Model { const COM = 0; const CO_UK = 1; const DE = 2; public static function mapConnection($region){ $maps = [ self::COM => 'com_db', self::CO_UK => 'couk_db', self::DE => 'de_db' ]; if (! in_array($region,array_keys($maps))){ return $maps[self::COM]; } return $maps[$region]; } ... public static function query($region = self::COM) { $model = new static; $model->connection = self::mapConnection($region); return $model->newQuery(); } } ~~~ ### 继承 Region ~~~php <?php namespace App\Models; use Illuminate\Database\Eloquent\Model; class Article extends Region { protected $connection = 'com'; protected $table = 'article'; } ~~~ ### 简洁查询 ~~~php <?php namespace App\Http\Controllers; use App\Models\Article; use Illuminate\Http\Request; use Illuminate\Support\Facades\Input; class IndexController extends Controller { public function index(){ $region = Input::get('region') ?: Region::COM; $articles = Article::query($region)->limit(10)->get(); return $articles; } } ~~~ ### 多表查询之前我们的实现原理就是重写 query, 修改 model 的 collection 进行实现，如果要映射多表，也是同样的办法我们先看我们重写的 query 方法 ~~~php public static function query($region = self::COM) { $model = new static; $model->connection = self::mapConnection($region); return $model->newQuery(); } ~~~ 我们要实现多表连接的话，只要修改 model 的 $table 就 ok 了 ~~~php public static function query($params1,$params2) { $model = new static; $model->table = self::mapTable($params1,$params2); return $model->newQuery(); } public static function mapTable($params1,$params2) { // 实现你的映射逻辑 } ~~~