モデル周り
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Schema::create('user_mails', function (Blueprint $table) { $table->bigIncrements('user_mail_id'); $table->bigInteger('user_id')->unsigned()->comment('ユーザーID'); $table->timestamps(); $table->softDeletes(); $table->foreign('user_id')->references('user_id')->on('users'); }); protected $fillable = [ 'user_id', ]; =User public function userMails(): HasMany { return $this->hasMany('App\Models\UserMail', 'user_id'); } =UserMail public function user(): BelongsTo { return $this->belongsTo('App\Models\User', 'user_id'); } |
データ抽出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $query = User::leftJoin('user_mails', function ($join) { $join->on('users.user_id', '=', 'user_mails.user_id')->whereNull('user_mails.deleted_at'); }); $query->select([ 'users.email', \DB::raw('(case when user_mails.user_mail_id is not null then 1 else 0 end) as is_mail') ]); $query->where('user_mails.user_mail_id', 1); $query->where(function ($subQuery) use ($keyword) { $subQuery->where('users.name', 'LIKE', "%{$keyword}%") ->orWhere('users.email', 'LIKE', "%{$keyword}%"); }); |