Get collection of parent class with the child that has maximum value of a column

I have 3 tables:

competitions [id, name, status]
clubs [id, name]
players [id, name, score, competition_id, club_id]

with appropriate relations on their respective model class. So I need a query that shows how many players who are competition winners at each club. For a player to be considered a competition winner, they must have the max score (and not draw with anyone).

So far I have tried, the following:

// App/Models/Player
public function winner(): bool
{
   return $this->score > $this->competition->players()->where('players.id', '<>', $this->id)->max('votes');
}

And in my controller:

public function index()
{
   $players = Player::all()->filter(function ($value, $key){
      return $value->winner();
   })->pluck('club_id');
   $clubWinners = $players->countBy();
   return Party::whereIn($players->unique())->get()->map(function ($item) use ($clubWinners){
      $item['no_of_wins'] = $clubWinners->get($item->id);
      return $item;
   })
}

which does do the trick. However, it's a terrible method, and very inefficient. I know there's a better solution out there, but I just can't think of any. Please any help will be appreciated.

Lumethys

3 years ago

Level 5

why are players table had both id and player_id?

SambhavP

3 years ago

Level 1

@Lumethys My bad. It was supposed to be club_id. I fixed it.

Lumethys

3 years ago

Level 5

@SambhavP seem like a rather odd choice of db setup. A competition have many players, each players belongs to a club, right?

To me Players is supposed to be a pivot table, because a player can join many competition, no?

So isnt it should be something like

clubs[ id, name]
members[ id, name, club_id]
competitions[ id, name, status]
competition_players[id, memebers_id, compertition_id, score]

which roughly translate to:

+a club have many members, but a member can only belong to 1 club

+a member can join many competition, and a competitions have many people join

SambhavP

3 years ago

Level 1

@Lumethys No actually, this is a much simplified example of what I'm currently working on. And the setup is basically not only does a player belong to a single club, but he also can only be part of a single competition.

Lumethys

3 years ago

Level 5

ok, sorry for the late reply, i forgot this thread

anyways, the players table seem to be a pivot table between clubs and competitions? Still kinda weird to me.

well, try this

     $club = Club::withCount([
        'players' => function (Builder $query) {
            $query->selectRaw('MAX(score), competition_id')->groupBy('competition_id');
        }
    ])
    ->find(1); //or any logic o get the club

1 like

Please or to participate in this conversation.