我還在學習laravel和雄辯，我有一個小問題......我有三個表：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');});我有模型魚以及與魚類型和照片的關系。它的工作，一切都很好，例如：$f = Fish::with('photos', 'fishery', 'type')->when($filters['userId'], function ($query) use ($filters) { return $query->where('user_id', $filters['userId']);});但我想從 db 獲得屬于用戶的每種類型中最長的魚，當然還有照片（急切加載）。我有 mysql 問題（是的，這很糟糕，但我加入 eloquent 沒有用：（）：$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";所以我有魚的身份證 - 但下一步是什么？相同的查詢“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();縮短的魚模型：class Fish extends Model{ public function type() { return $this->belongsTo('App\Models\FishType'); } public function fishery() { return $this->belongsTo('App\Models\Fishery'); }
1 回答
元芳怎么了
TA貢獻1798條經驗 獲得超7個贊
您正在執行兩個查詢，但這可以在一個查詢中完成。嘗試這個
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 關注
- 163 瀏覽
添加回答
舉報
0/150
提交
取消