Laravel Eloquent 数据库关联查询

一对一 HasOne

tables

1
2
3
4
5
6
- users
- id
- name
- avatars
- user_id
- image

HasOne 关联

1
2
3
4
5
6
7
8
9
// App\Models\User
public function avatar(): HasOne
{
// $this->hasOne('class_name', 'foreign_key', 'local_key');
// foreign_key默认是表名去掉 s 加上 '_id' 后缀
// local_key 默认是 id
return $this->hasOne(Avatar::class);
// 即 $this->hasOne(Avatar::class, 'user_id', 'id');
}

HasOne 反向关联

1
2
3
4
5
6
7
// App\Models\Avatar
public function user(): BelongsTo
{
// $this->belongsTo(User::class, 'foreign_key', 'owner_key');
// return $this->belongsTo(User::class, 'user_id', 'id');
return $this->belongsTo(User::class); // 省略 'foreign_key', 'owner_key' 参数
}

数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 新建
$user = User::create(['name', '...']);
Avatar::create([
'user_id' => $user->id,
'image' => '...',
]);

// 更新
$user = User::find(1);
// 更新关联数据
$user->avatar()->update(['image' => '...']);
// 更新模型
$user->update(['name' => '...']);

// 反向关联操作 belongsTo 的 associate/dissociate
$avatar = Avatar::find(1);
$avatar->user()->dissociate(); // 把 user_id 设为 null
$avatar->save(); // 保存更改

$user = User::find(10);
$avatar->user()->associate($user); // 把 user_id 改为 10
$avatar->save();

一对多 HasMany

tables

1
2
3
4
5
6
7
- posts
- id
- title
- comments
- id
- post_id
- comment

模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// App\Models\Post 关联
public function comments(): HasMany
{
// return $this->hasMany('class_name', 'foreign_key', 'local_key');
return $this->hasMany(Comment::class, 'post_id', 'id');
return $this->hasMany(Comment::class); // 使用默认参数
}

// App\Models\Comment 反向关联
public function post(): BelongsTo
{
// $this->belongsTo(Post::class, 'foreign_key', 'owner_key');
return $this->belongsTo(Post::class, 'post_id', 'id');
return $this->belongsTo(Post::class); // 省略 'foreign_key', 'owner_key' 参数
}

//TODO

子查询

1
2
3
4
5
6
7
User::query()->whereIn(
'id',
UserRolePivot::query()
->select('user_id')
->where('role_id', $roleId)
)->get();
// SELECT * FROM user WHERE id IN( SELECT user_id FROM user_role_pivot WHERE role_id = $roleId)

分组查询

1
2
3
4
5
6
7
8
User::query()
->where('verified', 1)
->where(function ($query) use ($keyword) {
$query->where('name', 'like', "%{$keyword}%")
->orWhere('email', 'like', "%{$keyword}%")
->orWhere('mobile', 'like', "%{$keyword}%");
})->get();
// SELECT * FROM user WHERE verified = 1 AND (name LIKE '%{$keyword}%' OR email LIKE '%{$keyword}%' OR mobile LIKE '%{$keyword}%')
作者

CoderPan

发布于

2023-01-30

更新于

2024-02-08

许可协议

评论