表格关联关系
<h1>表格关联关系</h1>
<h3>一对一</h3>
<p><code>users</code>表和<code>profiles</code>表通过<code>profiles.user_id</code>字段生成一对一关联</p>
<pre><code class="language-sql">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;</code></pre>
<p>对应的数据模以及数据仓库分别为:</p>
<pre><code class="language-php">&lt;?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
public function profile()
{
return $this-&gt;hasOne(Profile::class);
}
}</code></pre>
<pre><code class="language-php">&lt;?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Profile extends Model
{
public function user()
{
return $this-&gt;belongsTo(User::class);
}
}</code></pre>
<p>数据仓库</p>
<pre><code class="language-php">&lt;?php
namespace App\Admin\Repositories;
use Dcat\Admin\Repositories\EloquentRepository;
use App\Models\User as UserModel;
class User extends EloquentRepository
{
protected $eloquentClass = UserModel::class;
}</code></pre>
<p><a name="relation"></a></p>
<h4>三种关联数据表的方法</h4>
<p>通过以下三种方式的代码可以关联<code>profile</code>表数据:</p>
<p>方式一：直接使用数据仓库关联</p>
<pre><code class="language-php">use App\Admin\Repositories\User;
use Dcat\Admin\Grid;
// 关联 profile 表数据
$grid = Grid::make(User::with(['profile']), function (Grid $grid) {
$grid-&gt;id('ID')-&gt;sortable();
$grid-&gt;name();
$grid-&gt;email();
// 显示一对一数据
$grid-&gt;column('profile.age');
$grid-&gt;column('profile.gender');
$grid-&gt;created_at();
$grid-&gt;updated_at();
});</code></pre>
<p>方式二：使用<code>Model::with</code>方法关联</p>
<pre><code class="language-php">use App\Models\User;
use Dcat\Admin\Grid;
// 关联 profile 表数据
$grid = Grid::make(User::with(['profile']), function (Grid $grid) {
$grid-&gt;id('ID')-&gt;sortable();
...
});</code></pre>
<p>方式三：使用<code>Grid\Model</code>方法关联</p>
<pre><code class="language-php">use App\Admin\Repositories\User;
use Dcat\Admin\Grid;
$grid = Grid::make(new User(), function (Grid $grid) {
// 关联 profile 表数据
$grid-&gt;model()-&gt;with(['profile']);
$grid-&gt;id('ID')-&gt;sortable();
...
});</code></pre>
<h3>一对多</h3>
<p><code>posts</code>表和<code>comments</code>表通过<code>comments.post_id</code>字段生成一对多关联</p>
<pre><code class="language-sql">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;</code></pre>
<p>对应的数据模和数据仓库分别为:</p>
<pre><code class="language-php">&lt;?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
public function comments()
{
return $this-&gt;hasMany(Comment::class);
}
}</code></pre>
<pre><code class="language-php">&lt;?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Comment extends Model
{
public function post()
{
return $this-&gt;belongsTo(Post::class);
}
}</code></pre>
<pre><code class="language-php">&lt;?php
namespace App\Admin\Repositories;
use App\Models\Post as PostModel;
use Dcat\Admin\Repositories\EloquentRepository;
class Post extends EloquentRepository
{
protected $eloquentClass = PostModel::class;
}</code></pre>
<pre><code class="language-php">&lt;?php
namespace App\Admin\Repositories;
use App\Models\Comment as CommentModel;
use Dcat\Admin\Repositories\EloquentRepository;
class Comment extends EloquentRepository
{
protected $eloquentClass = CommentModel::class;
}</code></pre>
<p>同样这里支持上述的<a href="#relation">三种方式关联数据</a>，限于篇幅这里不再重复写所有用法</p>
<p>Post表格</p>
<pre><code class="language-php">use App\Admin\Repositories\Post;
// 关联 comment 表数据
$grid = Grid::make(Post::with(['comments']), function (Grid $grid) {
$grid-&gt;id('id')-&gt;sortable();
$grid-&gt;title();
$grid-&gt;content();
$grid-&gt;comments('评论数')-&gt;display(function ($comments) {
$count = count($comments);
return &quot;&lt;span class='label label-warning'&gt;{$count}&lt;/span&gt;&quot;;
});
$grid-&gt;created_at();
$grid-&gt;updated_at();
});</code></pre>
<p>Comment表格</p>
<pre><code class="language-php">use App\Admin\Repositories\Comment;
// 关联 post 表数据
$grid = new Grid(Comment::with(['post']));
$grid-&gt;column('id');
$grid-&gt;column('post.title');
$grid-&gt;column('content');
$grid-&gt;created_at()-&gt;sortable();
$grid-&gt;updated_at();</code></pre>
<h3>多对多</h3>
<p><code>users</code>和<code>roles</code>表通过中间表<code>role_users</code>产生多对多关系</p>
<pre><code class="language-sql">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</code></pre>
<p>对应的数据模和数据仓库分别为:</p>
<p>User 模型</p>
<pre><code class="language-php">&lt;?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
public function roles()
{
return $this-&gt;belongsToMany(Role::class);
}
}</code></pre>
<p>Role 模型</p>
<pre><code class="language-php">&lt;?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Role extends Model
{
public function users()
{
return $this-&gt;belongsToMany(User::class);
}
}</code></pre>
<p>数据仓库</p>
<pre><code class="language-php">&lt;?php
namespace App\Admin\Repositories;
use App\Models\User as UserModel;
use Dcat\Admin\Repositories\EloquentRepository;
class User extends EloquentRepository
{
protected $eloquentClass = UserModel::class;
}</code></pre>
<p>同样这里支持上述的<a href="#relation">三种方式关联数据</a>，限于篇幅这里不再重复写所有用法</p>
<pre><code class="language-php">use App\Admin\Repositories\User;
// 关联 role 表数据
$grid = Grid::make(User::with('roles'), function (Grid $grid) {
$grid-&gt;id('ID')-&gt;sortable();
$grid-&gt;username();
$grid-&gt;name();
$grid-&gt;roles()-&gt;pluck('name')-&gt;label();
$grid-&gt;created_at();
$grid-&gt;updated_at();
});</code></pre>