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