Symfony2 - 存储库中的自定义查询，用于多对多关系

Question

I have a manyToMany relationship between players and coaches. Each player can have many coaches and each coach can have many players.

I want to create custom query to recuperate all the players that are linked to a particular coach but I really don't know how to do that.

I really need to create a custom query in a repository that will recuperate all the players linked to a particular coach.

Here's the canvas of my repository function in OSC\User\Entity\PlayerRepository

public function findPlayersOfCurrentUser($user) { //The user id is the id linked to the player
        return $this->_em->createQuery('


        ');

    }

All my many-to-many relationships are working and therefore I have a table (named user_player) like the following that is generated:

user_id     player_id
   1            1

Finally, my question would be: What is the SQL query to SELECT all the players in the OSCUserBundle:Player that have $user->getId() as a Coach ?

I know what to do in words(

SELECT all the players from OSCUserBundle:Player WHERE id IN (SELECT all the player_id FROM user_player WHERE user_id = $user->getId()

) but not in via createQuery or createQueryBuilder...

Answer 1

// PlayerRepository
public function findPlayersOfCurrentUser(User $coach) 
{ 
    return $this->createQueryBuilder("o")
        ->innerJoin("o.coaches", "c", "WITH", "c=:coach")
            ->setParameter("coach", $coach)
        ->getQuery()->getResult() ;

}

Presuming that user and coach are related using aliases coaches and players (plurals, as they should be).