MySQL query to eloquent

I am building a scores tracker, please see question how to track score gains in mysql for more details.

In that question I asked how to get specific data out of my database, I got a great answer which returns the correct data, however I now need to add this into laravel.

I was hoping to just use whereRaw() but that threw up lots of errors, and most likely isn’t the best solution anyway.

so far I have setup a hasMany relationship between Player and SkillScores

class Player extends Model {         public function scores()     {         return $this->hasMany(SkillScores::class);     }      } 

I have added a scopeWithskills() trait to lookup the skill name:

class SkillScores extends Model {     public function scopeWithSkills(Builder $query)     {         $query->leftJoinSub(             'select * FROM skills',             'skills',             'skills.id',             'skill_scores.skill_id'         );     } } 

I am sending that to my view in PlayerController:

class PlayerController extends Controller {     public function index()     {         return view('player', [             'players' => auth()->user()->players()         ]);     }      public function show(Player $player)     {         return view('player.show', [             'player' => $player,             'scores' => $player->scores()->withSkills()->get()         ]);     } } 

What other relationships do I need to setup to meet the below query?

WITH cte AS ( SELECT id, player_id, skill_id,        FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) score,         FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) - FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at ASC) gain,        ROW_NUMBER() OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) rn FROM skill_scores WHERE created_at BETWEEN @current_date - INTERVAL @interval DAY AND @current_date ) SELECT cte.player_id, skills.name, cte.score, cte.gain FROM cte JOIN skills ON skills.id = cte.skill_id WHERE rn = 1 ORDER BY player_id, name; 

EDIT:-

I have now managed to get this to work by using DB::select() instead of whereRaw() but can’t help but think there must be a better way of achieving this with relationships:-

public function gains($interval = 1) {             return DB::select("WITH cte AS (         SELECT id, player_id, skill_id,                FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) score,                 FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) - FIRST_VALUE(score) OVER (PARTITION BY player_id, skill_id ORDER BY created_at ASC) gain,                ROW_NUMBER() OVER (PARTITION BY player_id, skill_id ORDER BY created_at DESC) rn         FROM skill_scores2         WHERE created_at BETWEEN now() - INTERVAL '$interval' DAY  AND now() AND player_id = '$this->id'         )         SELECT cte.player_id, skills.name, cte.score, cte.gain         FROM cte         JOIN skills ON skills.id = cte.skill_id         WHERE rn = 1         ORDER BY player_id, name"); } 
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.