CODEWITHSUNDEEP
symfonydoctrinequery-builder
mlwacosmos
mlwacosmos

Reputation: 4541

QueryBuilder and entity with ManyToMany relation

In the database, I have a table User (idUser, name...) and a table Role(IdRole, description..). Between the two of them there is a link table HasRole (idUser, idRole).

Generating the entity with the Symfony command line, the HasRole entity is not generated. Instead, in the User Entity I have this on the role property :

* @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="MyBundle\Entity\Role", inversedBy="utilisateur")
 * @ORM\JoinTable(name="hasrole",
 *   joinColumns={
 *     @ORM\JoinColumn(name="UTILISATEUR", referencedColumnName="ID")
 *   },
 *   inverseJoinColumns={
 *     @ORM\JoinColumn(name="ROLE", referencedColumnName="ID")
 *   }
 * )

I would like to create in the UserRepository a method that returns all of the users for one id Role.

It should starts like this :

$qb = $this->getEntityManager()->createQueryBuilder()
        ->select('utilisateur')
        ->from('ACCUEILBundle:User', 'utilisateur');

can u help ?

Ty

Upvotes: 0

Views: 137

Answers (2)

BentCoder
BentCoder

Reputation: 12730

I'm giving you an example so that you can work on it to adopt it into your case. I've removed most of entity properties/annotation to keep it simple for you to get the picture.

In example below, there is Student (M) to (N) Subject relationship. To achieve M-N, you can Student (1) to (N) StudentSubject (N) to (1) Subject so the relationships are held in StudentSubject entity.

Student

class Student
{
    protected $id;

    /**
     * @ORM\OneToMany(targetEntity="StudentSubject", mappedBy="studentMap", cascade={"persist", "remove"})
     */
    protected $studentInverse;
}

Subject

class Subject
{
    protected $id;

    /**
     * @ORM\OneToMany(targetEntity="StudentSubject", mappedBy="subjectMap", cascade={"persist", "remove"})
     */
    protected $subjectInverse;
}

StudentSubject

/**
 * @ORM\Entity(repositoryClass="School\FrontendBundle\Repository\StudentSubjectRepository")
 */
class StudentSubject
{
    protected $id;

    /**
     * @ORM\ManyToOne(targetEntity="Student", inversedBy="studentInverse")
     * @ORM\JoinColumn(name="student", referencedColumnName="id", nullable=false, onDelete="CASCADE")
     */
    protected $studentMap;

    /**
     * @ORM\ManyToOne(targetEntity="Subject", inversedBy="subjectInverse")
     * @ORM\JoinColumn(name="subject", referencedColumnName="id", nullable=false, onDelete="CASCADE")
     */
    protected $subjectMap;
}

Example Query:

This query select only given fields from Student and Subject entities.

class StudentSubjectRepository extends EntityRepository
{
    public function findAll()
    {
        $fields = [
            'st.id AS stId',
            'st.studentId AS stStId',
            'sb.id AS sbId',
            'sb.code AS sbCode',
        ];

        return
            $this
                ->createQueryBuilder('ss')
                ->select($fields)
                ->join('ss.studentMap', 'st')
                ->join('ss.subjectMap', 'sb')
                ->addOrderBy('st.studentId', 'ASC')
                ->addOrderBy('sb.code', 'ASC')
                ->getQuery()
                ->getResult(Query::HYDRATE_SCALAR);
    }
}

Upvotes: 0

Emanuel Oster
Emanuel Oster

Reputation: 1296

If you use Doctrine to generate your ManyToMany join table, then indeed you will not have an entity for that.

If you need to have the join table as an entity (for example because you store additional data in that table), then you should create relationship in the following way:

entityA <-OneToMany-> joinEntity <-ManyToOne-> entityB

However in your case, it should suffice to just get the Role with the desired ID and then get the users from that:

$role = $entityManager->getRepository('Role')->find($id);
$users = $role->getUsers(); //(or getUtilisatuers(), if I see correctly)

Upvotes: 1

Related Questions