Ad

Laravel Query Builder - Select Top 1 Row From Another Table

- 1 answer

I have this SQL query for MySQL which works fine. But I need to rewrite it using query builder and need to avoid DB::raw() completely because development database is different from production. I know far from ideal, but unfortunately it is what it is.

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

And I'm struggling how to rewrite the personal_best part. I have table of performances for athletes and I need to select only the best performance for each athletes as his personal best.

I was trying to search for answer but all of the answers I found included raw adding raw SQL.

Any ideas or hint would be much appreciated.

Thank you in advance!

So I accepted I might have to use Eloquent for this, but still having trouble to progress. Heres my code:

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;
}
Ad

Answer

Create a new connection at database.php like mysql_dev for development parameters.

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

try like this without raw,

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

Ad
source: stackoverflow.com
Ad