场景说明
在laravel开发过程中,在项目到达一定的程度,经常要做分库或者分表进行项目的调整,这里,来说一下遇到这样的问题之后怎么进行多库连接和多表映射
Elequent查询原理
平时我们进行基础查询:
$user = User::find(1);
$article = Article::where('author_id',5)->first();
Elequent是如何进行操作的呢?
先看基础Model的实现
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;选择连接的库,具体请参考数据表名称
从上面看出,我们通过查询时,无论是静态方法还是实体方法,都无一例外调用到了newQuery进行查询操作.
所以一个很方便的方法就是重写Model的query方法
配置多库
config/database.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
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
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Article extends Region
{
protected $connection = 'com';
protected $table = 'article';
}
简洁查询
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方法
public static function query($region = self::COM)
{
$model = new static;
$model->connection = self::mapConnection($region);
return $model->newQuery();
}
我们要实现多表连接的话,只要修改model的$table就ok了
public static function query($params1,$params2)
{
$model = new static;
$model->table = self::mapTable($params1,$params2);
return $model->newQuery();
}
public static function mapTable($params1,$params2)
{
// 实现你的映射逻辑
}
本作品采用《CC 协议》，转载必须注明作者和本文链接