Relation between 3 tables in Laravel 5.7

I have three tables :

PLAYERS with a team_id

TEAMS

SCORES with a player_id 

The thing is :

My teams have players who have scores and I would like to make a ranking of teams. So basically get the best score by players and make the sum of it if a team has several players.

For example :

TEAM A has player 1 and player 2. Player 1 has 3 scores (300, 150 and 500 for example) and I'd like to keep only the best one (so 500).

Do you have any idea how I can do that ? As there's no direct relation between teams and scores or between players and scores, I don't understand how I can make the link between these 3.

Thanks for your help !

EDIT

Score Model

class Score extends Model
{
protected $fillable = [
    'value', 'player_id'
];

public function player()
{
    return $this->belongsTo('App\Player');
}

public function players_scores()
{
    return $this->hasManyThrough('App\Team', 'App\Player');
}

}

Player model

class Player extends Model
{
protected $fillable = [
  'name','email','team_id'
];

/**
 * Get the team of the player
 */
public function team()
{
    return $this->belongsTo('App\Team');
}

/**
 * Get the scores of the player
 *
 * @return \Illuminate\Database\Eloquent\Relations\HasMany
 */
public function scores()
{
    return $this->hasMany('App\Score');
}

/**
 * Get the maximum score from the player
 *
 * @return mixed
 */
public function getBestScoreAttribute()
{
    return $this->scores->max('value');
}


}

Team model

class Team extends Model
{
protected $fillable = ['name','logo'];

protected $appends = ['score'];

public function players()
{
    return $this->hasMany('App\Player');
}

/*
 * Collect all the team players scores
 */
public function players_scores()
{
    return $this->hasManyThrough('App\Score', 'App\Player');
}

public function scores()
{
    return $this->hasMany('App\Score');
}

/*
 * Sum the score of all team players to get the team score
 */
public function getScoreAttribute()
{
    return $this->players_scores->sum('value');
}   

}

First you should have defined a hasMany function on the Player model pointing to the Score model. Then you can query the Player's best score by doing $player->scores()->max('value') (assuming that $value is the attribute that holds the actual score value in the Score model).

Once you have that, you can use that snippet for defining an accessor, that way you can do something like $player->bestScore for getting the maximum score for a player.

Then having a way yo query the Player's best score, you should have defined a HasMany relationship on the Team model pointing to the Player model. The query for getting the team score would be like $team->players->sum('max_score'). You can also define a mutator for this attribute so you can access this snippet like $team->score.

Finally, for ordering all teams it would be:

  return Team::all()->sortByDesc('score');

Relations between 3 tables in Laravel 5.7, I have three tables : PLAYERS with a team_id TEAMS SCORES with a player_id. The thing is : My teams have players who have scores and I would like to make  I am trying to delete data on 3 relation table using laravel 5.7, when I am trying it on 2 relation table, it's run well, but when I'm trying it in 3 relation table, it does not work. this is my t


Try this simple way

First select all the players of a particular team like

$players=PLAYERS::where('team_id',1)->get();//1 is a sample value pass your team_id

Then use foreach to iterate each player id and get the highest score

foreach($players as $player)
{
   $score=SCORES::where('player_id',$player)->orderBy('score', 'desc')->value('score'); // gets only the highest score of the player
   $total=$total+$score;//Sum of the highest score
}

Then if you want to insert into the table means

TEAMS::create(['team_id'=>1,'score'=>$total]);

Hope it helps:)

Eloquent: Relationships - Laravel, How to fetch data from two or more by using multiple join in Laravel 5.8 and display on web Duration: 9:17 Posted: Apr 21, 2019 I'm still discovering laravel and I like the eloquent system. However, I'm stuck with my Mysql structure and how to make relationships in laravel. Let's say with have 3 tables : one to represent apartment, we call it "lot". one to represent floor, we call it "etage". one to represent surface, we call it "fraction".


In case someone has the same issue, I found the solution :

static function getDailyTeams(int $limit = 10)
{        
    $callback = function($query) {
        $query->whereDate('scores.created_at', date('Y-m-d'));
    };
    $player = function($query) use ($callback) {
        $query->whereHas('scores', $callback)->with(['scores' => $callback]);
    };
    $teams = Team::with(['players' => $player])->get()->sortByDesc(function ($team) {
        return $team->players->sum('best_score');
    }
    );
    return $teams;  
}

Laravel 5.8 - Join Multiple Table, in this laravel video tutorial we learn how to apply join or more than 2 tables and we will Duration: 6:19 Posted: Oct 14, 2018 To define this relationship, three database tables are needed: users, roles, and role_user. The role_user table is derived from the alphabetical order of the related model names, and contains the user_id and role_id columns:


Laravel 5.7 tutorial #16 - how to use join on multiple tables, For instance, more than one user may have the role of "Admin". In defining this relationship, we need three database tables: users, roles, and  Laravel 5.7 One to Many Relationship. In this Example Learn To one to many relationship laravel model, hasmany laravel 5.7 tutorial, create hasmany relationship laravel, laravel one to many insert. Example details: In this Example we have 2 models (Stranger and Mobile), and 2 tables (strangers and mobiles). Business Rules:


Laravel (5.7) Eloquent Relationships, Hello, I have Six tables in a sequence and I want to join them with Eloquent relationships. While I am trying to do this Laravel throws an  I am building a Twitter-like app. There is a Feed in which I want to only show posts of Users who I follow. I tried everything with joins, but nothing seems to work. I have 3 tables: Users, Follo


How to Join Multiple Tables with Eloquent Relationships, I have 3 tables users -id -email -password and other columns user_details -id returns whole users table and user_details table but i only want to select first_name and last_name https://laravel.com/docs/5.7/queries#joins. To define this relationship, three database tables are needed: users, roles, and role_user. The role_user table is derived from the alphabetical order of the related model names, and contains the user_id and role_id columns. Many-to-many relationships are defined by writing a method that returns the result of the belongsToMany method.