findBy with JOIN criteria in Symfony2

问题:

I have 3 simple tables: user, role, user_x_role with Many-To-Many relation. I have 2 entities: User and Role. User entity has $userRoles property with relation annotation. In Controller I need to fetch all users with specific role. But I don't know how to use JOIN in controller. Current wrong code:

$role = $this->getDoctrine()->getRepository('TestBackEndBundle:Role');
$roles = $role->findBy(array('name' => 'ROLE_PARTNER'));

$user = $this->getDoctrine()->getRepository('TestBackEndBundle:User');
$partners = $user->findBy(array('userRoles' => $roles));

It thows "Undefined index: joinColumns in ...". But I have joinColumns in User entity:

/**
 * @ORM\ManyToMany(targetEntity="Role")
 * @ORM\JoinTable(name="user_x_role",
 *     joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="CASCADE", onUpdate="CASCADE")},
 *     inverseJoinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id", onDelete="CASCADE", onUpdate="CASCADE")}
 * )
 * @var ArrayCollection
 */
protected $userRoles;

回答1:

IMO the best way for it is create your own repository for User entity. Then in that repository create method like "getUsersByRole" where you make the query you want with query builder.

 $qb = $this->getEntityManager()->createQueryBuilder();
 $qb->select('u')
     ->from('\namespace\for\User', 'u')
     ->join('u.roles', 'r')
     ->where(...)

 return $qb->getQuery()->getResult();