Laravel Eloquent 数据库关联查询

一对一 HasOne

tables

- users
  - id
  - name
- avatars
  - user_id
  - image

HasOne 关联

// 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 反向关联

// 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' 参数
}

数据操作

// 新建
$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

- posts
  - id
  - title
- comments
  - id
  - post_id
  - comment

模型

// 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

子查询

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)

分组查询

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}%')