iJamesPHP2 iJamesPHP2 - 6 months ago 43
SQL Question

Changing raw SQL query into Laravel Query Builder object

I have this query:

public function rank()
{
$sql = "
select id, points, team_name,
(select count(*)
from teams t2
where t2.points > t.points or
(t2.points = t.points and t2.id <= t.id)
) as rank
from teams t
where id = ?;
";
$ranks = DB::select($sql, [$this->id]);
foreach ($ranks as $rank) {
return $rank->rank;
}
}


I would like to change it into a Laravel query builder as opposed to a raw query, how would I do this?

Answer

This should work.

$select_raw = <<<SQL
    id, points, team_name,(
    select count(*)
    from teams t2
    where t2.points > t.points or
        (t2.points = t.points and t2.id <= t.id)
    ) as rank
SQL;

$ranks = Team::where('id', $this->id)->selectRaw($select_raw)->get();
Comments