数据库和数据模型

One To One	User hasOne Phone	Phone belongsTo User	hasOne('model', 'own_id', 'id')
One To Many	Post hasMany Comments	Comment belongsTo Post
Many to Many	User belongsToMany Role	Role belongsToMany User
has Many through
O2M Polymorphic relation
M2M Polymorphic relation

load-with,attach-detach

数据关联模型：
***One To One: User hasOn  Phone
class User extends Model { 
    function phone() { 
        return $this->hasOne('App\Phone', 'owner_id', 'id');  //phones.own_id = users.id
    }
}
class Phone extends Model {
    function user() { return $this->belongsTo('App\User'); }
}

***One To Many: Post HasMany Comments 
class Post extends Model {
    function comments() { return $this->hasMany('App\Comments'); }
}
class Comment extends Model {
    function post() { return $this->belongsTo('App\Post'); }
}

Many To One 
Many To Many.
attach,detach,sync只对Many To Many


三个关联表： feed_topics, users, feed_topic_user_links
class FeedTopic extends Model {
    public function users() : BelongsToMany
    {
        $table = (new FeedTopicUserLink)->getTable();
        return $this
            ->belongsToMany(User::class, $table, 'topic_id', 'user_id')
            ->withPivot('index', Model::CREATED_AT, 'following_at', 'can_pub')
            ->using(FeedTopicUserLink::class);
    }
}

调用vendor\laravel\framework\src\Illuminate\Database\Query的toSql方法：
$query->toSql();

DB::connection()->enableQueryLog();
$count = DB::table('test')
            ->whereNull('deleted_at')
            ->where('id', '=', 3)
            ->where('Name', '=', '测试')
            ->count();
print_r(DB::getQueryLog());

设置model事件observer

$data = User::distinct()->offset(10)->limit(10)->get()->toArray();
DB::table('users')->first();
DB::table('users')->pluck('name');              //返回一个字符串
DB::table('users')->lists('name');              //返回字符串数组
DB::table('users')->lists('name', 'email');     //返回数组，email => name

with关联表查询
$category = Category::with('goods')->find($categoryId);
$category = Category::with(['goods' => function ($query) {
    $query->where('is_sale', true);
}])->find($categoryId);

Affair::query()
->when($request->has('offset'), 
   static function (Builder $builder) use ($request) {
     return $builder->offset($request->query('offset', 0))
        ->limit($request->query('limit', config('app.data_limit')))
        ->get();
}, static function (Builder $builder) use ($request) {
     return $builder->paginate($request->query('limit', config('app.data_limit')));
});
            
class UserObserver {
    public function creating(User $user) {}
    public function created(User $user) {}
    public function updateing(User $user) {}
    public function updated(User $user) {}
    public function deleteing(User $user) {}
    public function deleted(User $user) {}
    public function retoring(User $user) {}
    public function restored(User $user) {}
}
class UserServiceProvider extends ServiceProvider
{
    public function boot() {
        User::observer(UserObserver);
    }
    public function register() {
    }
}

Model

连接多个数据库
config/database.php
'mysql_old' = [
    'driver'    => 'mysql',
    'host'      => env('DB_HOST_OLD', '127.0.0.1');
    'post'      => env('DB_POSR_OLD', '3306');
];
$user = DB::connection('mysql_old')->select('select * from users');
DB::connection('mysql_old')->table('users')->insert()

数据库读写分离
'mysql' => [
    'driver'    => 'mysql',
    'read'      => [
        'host'  => env('DB_HOST_READ', '127.0.0.1');
    ],
    'write'      => [
        'host'  => env('DB_HOST_WRITE', '127.0.0.1');
    ],
];

手动执行SQL语句:
DB门面执行原生语句
DB::statement('drop table user');
DB::statement('create table users(...) ...');
$users = DB::select('select * from users');
DB::insert('...');
DB::update('...');
DB::delete('...');

多数据库:
DB::connection('mysql_old')->select('...');
DB::connection('mysql_old')->insert('...');

$users = DB::table('users')->where('name', $name)->get();
$flag = DB::table('users')->insert([
    'name'  => 'xiao',
    'email' => 'a@163.com',
    'passwd'=> bcrypt('secret');
]);

json查询
DB::table('users')->where('options->language', 'en')->get();

两段提交：
手动执行事务
DB::beginTransaction();
DB::commit();
DB::rollback();

数据库事务
DB::transaction(function() {
    DB::table('users')->update(['votes'=>1]);
    DB::table('posts')->delete();
});


try{
    DB::transaction(function ()use($id,$data) {
        DB::table('supplier')-> where('id',$id) -> update($data);
    });
}catch(\Illuminate\Database\QueryException $ex){
    return back() -> with(['info' => '抱歉，修改失败，请稍后重试！']);
}
return redirect('admin/buy/supplier') -> with(['info' => '修改成功']);

DB::beginTransaction(); //事务开始
try {
    DB::insert(...);
    DB::insert(...);
    DB::insert(...);
    
    DB::commit();
} catch(\Illuminate\Database\QueryException $ex) {
    DB::rollback();

    // something went wrong
}

改变模型的缺省定义
class Article extends model
{
    protected $connection = 'mysql_new';
    protected $table = "article";
    protected $primaryKey = 'post_id';
    public $incrementing = false;
    protected $keyType = 'string';
    public $timestamps = false;     //表不包含create_at, update_at字段
    protected $dateFormat = 'U';    //默认是Y-m-d H:i:s, 设置为unix时间戳
    
    protected $guarded = ['user_id'];
}
$post = new Post($request->all());
$post->user_id = 0;                 //单独设置变量,guarded设置后
$post->save();

$post->fill($request->all());       //更新模型
$post->save();

访问器
public function getDisplayNameAttribute()
{
    return $this->nickname ? $this->nickname : $this->name;
}
定义模型读属性 $user->display_name;

修改器
public function setCardNoAttribute($value)
{
    $value = str_replace(' ', '', $value);
    $this->attribute['card_no'] = encrypt($value);
}
定义模型写属性 $user->card_no = '12333343';

全局作用域, 重载boot
protected statis function boot()
{
    parent::boot();

    static::addGlobalScope('email_verified_at_scope', function(Builder $builder) {
        return $builder->whereNotNull('email_verified_at');
    });
}

取消全局作用域：
User::withoutGlobalScope(EmailVerifiedAtScope::class)->get(); # 指定类
User::withoutGlobalScope('email_verified_at_scope')->get();   # 匿名函数
User::withoutGlobalScopes()->get();  # 移除所有全局作用域
User::withoutGlobalScopes([FirstScope::class, SecondScope::class])->get();   # 移除多个类/匿名函数

局部作用域
public function scopePopular(Builder $query) 
{
    return $query->where('views', '>', '0')->orderBy('views','desc');
}
public function scopeActive(Builder $query) 
{
    return $query->where('status', Post::ACTIVE);
}
调用：$post = Post::active->popular->find(10);

动态作用域
public function scopeOfType(Builder $query, $tpe)
{
    reutrn $query->where('type', $type);
}
调用：$post = Post::active->ofType(Post::Article)->get();

静态方法监听模型事件
订阅者监听模型事件
观察者监听模型事件

远程一对多  country - user -post

软删除

//1、第一种方法
Post::find(5)->delete();
//2、第二种方法(主键删除)
Post::destroy(5)
Post::destroy(1,2,3)
//3、第三种方法
Post::where('views', 0)->delete();

软删除
数据库中添加deleted_at字段
use Illuminate\Database\Eloquent\SoftDeletes;
class Post extends Model
{
    use SoftDeletes;
}

包含软删除查询
Post::withTrashed()->find('1');
Post::withTrashed()->where('id','>',1)->get();
Post::withTrashed()->all();
只包含软删除记录：
Post::onlyTrashed()->where('id', 1)-get();

软删除查询恢复
//恢复单个
Post::withTrashed()->find(1)->restore();
//恢复多个模型
Post::withTrashed()->where('id','>',1)->restore();
//恢复所有模型
Post::withTrashed()->restore();

强制删除
//删除单个
Post::withTrashed()->find(1)->forceDelete();
//删除多个模型
Post::withTrashed()->where('id','>',1)->forceDelete();
//删除所有模型
Post::withTrashed()->forceDelete();

关联模型
public function user()
{
    return $this->belongsTo('App\User')->withTrashed();
}

select * from `feeds` 
inner join `feed_topic_links` 
    on `feeds`.`id` = `feed_topic_links`.`feed_id` and 
       `feed_topic_links`.`topic_id` = ? 
where `audit_status` = ? and `feeds`.`deleted_at` is null 
order by `id` desc

$feeds = $model
    ->query()
    ->with(['topics', 'user'])
    ->when($topic = $request->query('topic'), function ($query) use ($topic) {
        $query->join('feed_topic_links', function ($query) use ($topic) {
            return $query->on('feeds.id', 'feed_topic_links.feed_id')
                ->where('feed_topic_links.topic_id', $topic);
        });
    })
    ->when($request->has('audit_status'), function ($query) use($request) {
        $status = $request->query('audit_status');
        if ($status == 3) {
            return $query->onlyTrashed();
        }
        return $query->where('audit_status', $status);
    })
    ->orderBy('id', 'desc');

Laravel中RBAC的modle层(超简)

关于用户model层
/** * 用户有哪些角色 */ 
public function roles() { // 使用withPivot可以把关系表中的字段获取出来 
    return $this
        ->belongsToMany(\App\AdminRole::class, 'admin_role_user', 'user_id', 'role_id')
        ->withPivot(['user_id', 'role_id']); 
}

/** * 用户是否有某个角色(某些角色) */ 
public function isInRoles($roles) { 
    return !!$roles->intersect($this->roles)->count(); 
}

/** * 用户是否有某个权限 */ 
public function hasPermission($permission) { 
    return $this->isInRoles($permission->roles); 
}

/** * 给用户分配角色 */ 
public function assignRole($role) { 
    return $this->roles()->save($role); 
}

/** * 取消用户的角色 */ 
public function deleteRole($role) { 
    return $this->roles()->detach($role); 
}

关于角色层的model
/** * 当前角色的所有权限 */ 
public function permissions() { 
    return $this
        ->belongsToMany(\App\AdminPermission::class, 'admin_permission_role', 'role_id', 'permission_id')
        ->withPivot(['role_id', 'permission_id']); 
}

/** * 给角色分配权限 */ 
public function grantPermission($permission) { 
    return $this->permissions()->save($permission); 
}

/** * 取消角色的权限 */ 
public function deletePermission($permission) { 
    return $this->permissions()->detach($permission); 
}

/** * 判断角色是否有权限 */ 
public function hasPermission($permission) { 
    return $this->permissions->contains($permission); 
}

关于权限层的model
/** * 当前权限属于哪个角色 */ 
public function roles() { 
    return $this
        ->belongsToMany(\App\AdminRole::class, 'admin_permission_role', 'permission_id', 'role_id') 
        ->withPivot(['role_id', 'permission']); 
}