表格关联关系

一对一

users表和profiles表通过profiles.user_id字段生成一对一关联

CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `profiles` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`age` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`gender` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

对应的数据模以及数据仓库分别为:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Profile extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

数据仓库

<?php

namespace App\Admin\Repositories;

use Dcat\Admin\Repositories\EloquentRepository;
use App\Models\User as UserModel;

class User extends EloquentRepository
{
    protected $eloquentClass = UserModel::class;
}

三种关联数据表的方法

通过以下三种方式的代码可以关联profile表数据:

方式一：直接使用数据仓库关联

use App\Admin\Repositories\User;
use Dcat\Admin\Grid;

// 关联 profile 表数据
$grid = Grid::make(User::with(['profile']), function (Grid $grid) {    
    $grid->id('ID')->sortable();
    
    $grid->name();
    $grid->email();
    
    // 显示一对一数据
    $grid->column('profile.age');
    $grid->column('profile.gender');
    
    $grid->created_at();
    $grid->updated_at();
});

方式二：使用Model::with方法关联

use App\Models\User;
use Dcat\Admin\Grid;

// 关联 profile 表数据
$grid = Grid::make(User::with(['profile']), function (Grid $grid) {    
    $grid->id('ID')->sortable();
    
    ...
});

方式三：使用Grid\Model方法关联

use App\Admin\Repositories\User;
use Dcat\Admin\Grid;


$grid = Grid::make(new User(), function (Grid $grid) {
	// 关联 profile 表数据
	$grid->model()->with(['profile']);
    
    $grid->id('ID')->sortable();
    
    ...
});

一对多

posts表和comments表通过comments.post_id字段生成一对多关联

CREATE TABLE `posts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`content` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `comments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`post_id` int(10) unsigned NOT NULL,
`content` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

对应的数据模和数据仓库分别为:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Comment extends Model
{
    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

<?php

namespace App\Admin\Repositories;

use App\Models\Post as PostModel;
use Dcat\Admin\Repositories\EloquentRepository;

class Post extends EloquentRepository
{
    protected $eloquentClass = PostModel::class;
}

<?php

namespace App\Admin\Repositories;

use App\Models\Comment as CommentModel;
use Dcat\Admin\Repositories\EloquentRepository;

class Comment extends EloquentRepository
{
    protected $eloquentClass = CommentModel::class;
}

同样这里支持上述的三种方式关联数据，限于篇幅这里不再重复写所有用法

Post表格

use App\Admin\Repositories\Post;

// 关联 comment 表数据
$grid = Grid::make(Post::with(['comments']), function (Grid $grid) {
    $grid->id('id')->sortable();
    $grid->title();
    $grid->content();
    
    $grid->comments('评论数')->display(function ($comments) {
        $count = count($comments);
        
        return "<span class='label label-warning'>{$count}</span>";
    });
    
    $grid->created_at();
    $grid->updated_at();
});

Comment表格

use App\Admin\Repositories\Comment;

// 关联 post 表数据
$grid = new Grid(Comment::with(['post']));

$grid->column('id');
$grid->column('post.title');
$grid->column('content');

$grid->created_at()->sortable();
$grid->updated_at();

多对多

users和roles表通过中间表role_users产生多对多关系

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(190) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `roles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `role_users` (
  `role_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  KEY `role_users_role_id_user_id_index` (`role_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

对应的数据模和数据仓库分别为:

User 模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }
}

Role 模型

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Role extends Model
{
    public function users()
    {
        return $this->belongsToMany(User::class);
    }
}

数据仓库

<?php

namespace App\Admin\Repositories;

use App\Models\User as UserModel;
use Dcat\Admin\Repositories\EloquentRepository;

class User extends EloquentRepository
{
    protected $eloquentClass = UserModel::class;
}

同样这里支持上述的三种方式关联数据，限于篇幅这里不再重复写所有用法

use App\Admin\Repositories\User;

// 关联 role 表数据
$grid = Grid::make(User::with('roles'), function (Grid $grid) {
    $grid->id('ID')->sortable();
    $grid->username();
    $grid->name();
    
    $grid->roles()->pluck('name')->label();
    
    $grid->created_at();
    $grid->updated_at();
});

Last modified: 30 八月 2024