Laravel Eloquent - 按每种类型选择最大的 - join、max()

Question

我还在学习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');    }

Answer 1

您正在执行两个查询，但这可以在一个查询中完成。尝试这个

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();