findBy with JOIN criteria in Symfony2
19,420
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();
Author by
Alex Pliutau
Updated on July 23, 2022Comments
-
Alex Pliutau almost 2 years
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;