findBy with JOIN criteria in Symfony2

Question

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;

Answer 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();