时间戳

首页 > 解决方案 > Laravel Eloquent - 按每种类型选择最大的 - join、max() 和 group by

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

问题描述

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

    public function photos()
    {
        return $this->morphMany('App\Models\Photo', 'photoable');
    }

    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

照片模型和迁移：

 public function up()
    {
        Schema::create('photos', function (Blueprint $table) {

            $table->bigIncrements('id');
            $table->string('photoable_type');
            $table->bigInteger('photoable_id');
            $table->string('path');

        });
    }


class Photo extends Model
{

    public function photoable()
    {
        return $this->morphTo();
    }
}

它的工作一般，但也许我做错了？如何以更好的方式做到这一点？你能帮助我吗？:)

T。

标签： phpmysqllaraveleloquent

解决方案

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

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

推荐阅读