我還在學(xué)習(xí)laravel和雄辯，我有一個(gè)小問(wèn)題......我有三個(gè)表：Schema::create('fishes', function (Blueprint $table) { $table->bigIncrements('id'); $table->bigInteger('user_id')->unsigned(); $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); $table->bigInteger('type_id')->unsigned(); $table->foreign('type_id')->references('id')->on('fish_types')->onDelete('cascade'); $table->float('length');});Schema::create('fish_types', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('name')->unique(); $table->string('name_raw')->unique();});Schema::create('photos', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('photoable_type'); $table->string('photoable_id'); $table->string('path');});我有模型魚(yú)以及與魚(yú)類(lèi)型和照片的關(guān)系。它的工作，一切都很好，例如：$f = Fish::with('photos', 'fishery', 'type')->when($filters['userId'], function ($query) use ($filters) { return $query->where('user_id', $filters['userId']);});但我想從 db 獲得屬于用戶(hù)的每種類(lèi)型中最長(zhǎng)的魚(yú)，當(dāng)然還有照片（急切加載）。我有 mysql 問(wèn)題（是的，這很糟糕，但我加入 eloquent 沒(méi)有用：（）：$sql = "SELECT id FROM fishes f1 JOIN ( SELECT type_id , MAX(`length`) AS pb FROM fishes where user_id = 6 GROUP BY type_id ) AS f2 ON f1.type_id = f2.type_id and f2.pb = f1.length where f1.user_id = 6";所以我有魚(yú)的身份證 - 但下一步是什么？相同的查詢(xún)“whereIn(Column_name, Array)”？$sth = DB::getPdo()->prepare($sql);$sth->execute();$quy = $sth->fetchAll(\PDO::FETCH_COLUMN, 0);$f = Fish::with('photos', 'fishery', 'type') ->where('user_id', 6) ->whereIn('id', $quy)->get();縮短的魚(yú)模型：class Fish extends Model{ public function type() { return $this->belongsTo('App\Models\FishType'); } public function fishery() { return $this->belongsTo('App\Models\Fishery'); }
1 回答
元芳怎么了
TA貢獻(xiàn)1798條經(jīng)驗(yàn) 獲得超7個(gè)贊
您正在執(zhí)行兩個(gè)查詢(xún)，但這可以在一個(gè)查詢(xún)中完成。嘗試這個(gè)
Fish::with('photos', 'fishery', 'type')
->join(DB::raw('( SELECT type_id
, MAX(`length`) AS pb
FROM fishes
where user_id = 6
GROUP BY type_id
) AS f2'),function($join){
$join->on('fishes.type_id','=','f2.type_id')
->on('fishes.length','=','f2.pb');
})
->where('user_id', 6)->get();
代替
$sql = "
SELECT id
FROM fishes f1
JOIN **strong text**
( SELECT type_id
, MAX(`length`) AS pb
FROM fishes
where user_id = 6
GROUP BY type_id
) AS f2
ON f1.type_id = f2.type_id
and f2.pb = f1.length
where f1.user_id = 6
";
$sth = DB::getPdo()->prepare($sql);
$sth->execute();
$quy = $sth->fetchAll(\PDO::FETCH_COLUMN, 0);
$f = Fish::with('photos', 'fishery', 'type')
->where('user_id', 6)
->whereIn('id', $quy)->get();
- 1 回答
- 0 關(guān)注
- 161 瀏覽
添加回答
舉報(bào)
0/150
提交
取消