CODEWITHSUNDEEP
mysqlsqllaravel
HS'
HS'

Reputation: 37

Laravel - Query builder with subquery

So i have a pivot table like the following : 

match id
player id
score

And i want to query to get the number of wins/losses for a given user id. (wins based on user id with the highest score for a game)

In sql i would write this like : 

SELECT user_id, score 
from match_user matchu1 
where score = (select max(score) from match_user matchu2 where matchu1.match_id = matchu2.match_id)

How would i express this query in laravel, or is there a better method of doing this that i am missing ?

Upvotes: 1

Views: 106

Answers (3)

Kevin Bui
Kevin Bui

Reputation: 3045

This is to answer your true intention of asking this question, which is to get the number of wins for a single user, as you commented on my answer. The following the the best solution that I can think of for now:

class Match extends Model
{
    public function scopeWonBy($query, User $user)
    {
        return $query->selectRaw('matches.id, max(match_user.score) AS max_store, match_user.player_id AS player_id')
            ->join('match_user', 'matches.id', '=', 'match_user.match_id')
            ->groupBy('matches.id')
            ->having('player_id', $user->id);
    }
}

Later on, you can say:

$matches = Match::wonBy($user);
$count = Match::wonBy($user)->count();

Upvotes: 2

Kevin Bui
Kevin Bui

Reputation: 3045

There are multiple ways to achieve this. The easiest and cleaniest way to me is defining a relationship with pivot.

class Match extends Model
{
    public function players()
    {
        return $this->belongsToMany(User::class, 'match_user')->withPivot('score');
    }

    public function winner()
    {
        return $this->players
            ->sortByDesc(function ($player) {
                return $player->pivot->score;
            })
            ->first();
    }
}

Then you can simply get the winner by saying:

$match->winner();

Upvotes: 2

rattybag
rattybag

Reputation: 421

I'm not going to write your query for you as I don't want to provide an untested solution but the following code example should give you a good idea of how to implement subqueries with the query builder.

$q->where('price_date', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

Upvotes: 0

Related Questions