mysql - Laravel 查询生成器 - 从另一个表中选择前 1 行

Question

我有一个 MySQL 的 SQL 查询，它工作得很好。但我需要使用查询生成器重写它，并且需要完全避免 DB::raw() 因为开发数据库与生产数据库不同。我知道远非理想，但不幸的是事实就是如此。

SELECT athletes.*, 
     (
         SELECT performance
         FROM performances
         WHERE athletes.id = performances.athlete_id AND performances.event_id = 1
         ORDER BY performance DESC
         LIMIT 0,1
     ) AS personal_best
FROM athletes
ORDER BY personal_best DESC
Limit 0, 100

我正在努力如何重写 personal_best 部分。我有运动员的表现表，我只需要选择每个运动员的最佳表现作为他的个人最好成绩。

我试图寻找答案，但我找到的所有答案都包含原始添加原始 SQL。

任何想法或提示将不胜感激。

提前谢谢您!

所以我接受了我可能必须使用 Eloquent 来实现这一点，但仍然无法取得进展。这是我的代码:

class Athlete extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'athletes';

    /**
     * The primary key associated with the table.
     *
     * @var string
     */
    protected $primaryKey = 'id';

    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;

    /**
     * Get the performances for the Athelete post.
     *
     * @return HasMany
     */
    public function performances()
    {
        return $this->hasMany('App\EloquentModels\Performance', 'athlete_id');
    }
}

class Performance extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'performances';

    /**
     * The primary key associated with the table.
     *
     * @var string
     */
    protected $primaryKey = 'id';

    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;
}

Answer 1

在database.php处创建一个新连接，例如mysql_dev作为开发参数。

DB::connection('mysql_dev')->table('athletes')
    ->leftJoin('performances','athletes.id','performances.athlete_id')
    ->where('performances.event_id',1)
    ->groupBy('athletes.id')
    ->orderByDesc('personal_best')
    ->select('athletes.*',DB::raw('MAX(performances.performance) AS personal_best')
    ->paginate(100);

尝试不使用原始数据，

DB::connection('mysql_dev')->table('athletes')
    ->leftJoin('performances','athletes.id','performances.athlete_id')
    ->where('performances.event_id',1)
    ->groupBy('athletes.id')
    ->orderByDesc('performances.performance')
    ->select('athletes.*','performances.performance'
    ->paginate(100);