pdo query to sort the result depending on a common field of two tables

#php

#pdo

#mysql

ok this is really tricky and i cant wrap my brains around it, it would be great fi you can help me with it,

i am trying to write a query using pdo that does the below,

get all the users of a formation including the user from the formation table
join with the team of each user
get the players of each team in that formation and calculate their score sum
sort the result by position (from both formation and users table position field) from first to last (1,2,3,4..)

What i tried in Code

function getFormation(Request $request, Response $response) {
    $id = $request->getAttribute('id');

$sql = "SELECT u.id, u.formation, u.position FROM users AS u INNER JOIN formation AS f ON f.userid = u.id INNER JOIN teams as t ON t.userid = u.id WHERE f.id = :id OR u.formation =:id";

try {
    $db = new db();
    $db = $db->connect();
    $stmt = $db->prepare($sql);

    $stmt->bindParam(":id", $id);

    $stmt->execute();

    $formation = $stmt->fetch(PDO::FETCH_OBJ);

    $db = null;

    if(empty($formation)) {
        $response->getBody()->write
        ('
        {
            "error":
            {
                "status":"400",
                "message":"Invalid Request"
            }
        }');
    } else {
        $response->getBody()->write(json_encode($formation));
    }
} catch(PDOException $e) {
    $response->getBody()->write
    ('
    {
        "error":
        {
            "message":'. $e->getMessage() .'
        }
    }');
}
};

what i cannot make work is

joining the player table on which id should i join it ? i have 10
players id in the teams table
calculating the sum of all players scores
sort the result by position (from both formation and users table
position field) from first to last (1,2,3,4..)

result sample / data returned

formation position, formation user, total score 1(p1 score 1 + p2 score 1 + p3 score 1...), total score 2, total score 3, total score 4, total score 5, total score 6, team points

here is my db structure

========================================

formation table