Laravel 模型实现多库查询或者多表映射

场景说明

在laravel开发过程中,在项目到达一定的程度,经常要做分库或者分表进行项目的调整,这里,来说一下遇到这样的问题之后怎么进行多库连接和多表映射

Elequent查询原理

平时我们进行基础查询:

$user = User::find(1);
$article = Article::where('author_id',5)->first();

Elequent是如何进行操作的呢?

先看基础Model的实现

<?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;选择连接的库,具体请参考数据表名称

从上面看出,我们通过查询时,无论是静态方法还是实体方法,都无一例外调用到了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

<?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
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Article extends Region
{
    protected $connection = 'com';    
    protected $table = 'article';
}

简洁查询

<?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方法

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)
{
    // 实现你的映射逻辑
}

原文地址:https://www.timenotes.me/articles/code/23/laravel-model-to-realize-the-multi-query-or-table-mapping

巴拉巴拉小魔仙