Many-to-many select in Doctrine 2?

Question

I am unable to make a select function of a relationship many to many.

I have three tables

user and the fields id, name, email ...
system and the fields id, name, authentication
user_system and the fields id_user, id_system, access ...

using the doctrine console he bore me two entities, the User entity and the entity System

class User{

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=45, nullable=false)
 */
private $name;

/**
 * @var string
 *
 * @ORM\Column(name="email", type="string", length=60, nullable=false)
 */
private $email;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="date_create", type="datetime", nullable=false)
 */
private $dateCreate;

/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 */
private $id;

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="Procob\AutenticacaoBundle\Entity\System", inversedBy="idUser")
 * @ORM\JoinTable(name="user_system",
 *   joinColumns={
 *     @ORM\JoinColumn(name="id_user", referencedColumnName="id")
 *   },
 *   inverseJoinColumns={
 *     @ORM\JoinColumn(name="id_system", referencedColumnName="id")
 *   }
 * )
 */
private $system;

/**
 * Constructor
 */
public function __construct()
{
    $this->system = new \Doctrine\Common\Collections\ArrayCollection();
}


/**
 * Set name
 *
 * @param string $name
 * @return Usuario
 */
public function setName($name)
{
    $this->name = $name;

    return $this;
}

/**
 * Get name
 *
 * @return string 
 */
public function getName()
{
    return $this->name;
}


/**
 * Set email
 *
 * @param string $email
 * @return Usuario
 */
public function setEmail($email)
{
    $this->email = $email;

    return $this;
}

/**
 * Get email
 *
 * @return string 
 */
public function getEmail()
{
    return $this->email;
}

/**
 * Set dateCreate
 *
 * @param \DateTime $dateCreate
 * @return Usuario
 */
public function setDateCreate($dateCreate)
{
    $this->dateCreate = $dateCreate;

    return $this;
}

/**
 * Get dateCreate
 *
 * @return \DateTime 
 */
public function getDateCreate()
{
    return $this->dateCreate;
}

/**
 * Get id
 *
 * @return integer 
 */
public function getId()
{
    return $this->id;
}

/**
 * Add system
 *
 * @param \Procob\AutenticacaoBundle\Entity\System $system
 * @return Usuario
 */
public function addSystem(\Procob\AutenticacaoBundle\Entity\System $system)
{
    $this->system[] = $system;

    return $this;
}

/**
 * Remove system
 *
 * @param \Procob\AutenticacaoBundle\Entity\System $system
 */
public function removeSystem(\Procob\AutenticacaoBundle\Entity\System $system)
{
    $this->system->removeElement($system);
}

/**
 * Get system
 *
 * @return \Doctrine\Common\Collections\Collection 
 */
public function getSystem()
{
    return $this->system;
}
}




class System{

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=45, nullable=false)
 */
private $name;

/**
 * @var string
 *
 * @ORM\Column(name="authentication", type="string", length=10, nullable=false)
 */
private $authentication;

/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 */
private $id;

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="Procob\authenticationBundle\Entity\User", mappedBy="system")
 */
private $idUser;

/**
 * Constructor
 */
public function __construct()
{
    $this->idUser = new \Doctrine\Common\Collections\ArrayCollection();
}


/**
 * Set name
 *
 * @param string $name
 * @return system
 */
public function setName($name)
{
    $this->name = $name;

    return $this;
}

/**
 * Get name
 *
 * @return string 
 */
public function getName()
{
    return $this->name;
}

/**
 * Set authentication
 *
 * @param string $authentication
 * @return system
 */
public function setAuthentication($authentication)
{
    $this->authentication = $authentication;

    return $this;
}

/**
 * Get authentication
 *
 * @return string 
 */
public function getAuthentication()
{
    return $this->authentication;
}

/**
 * Get id
 *
 * @return integer 
 */
public function getId()
{
    return $this->id;
}

/**
 * Add idUser
 *
 * @param \Procob\authenticationBundle\Entity\User $idUser
 * @return system
 */
public function addIdUser(\Procob\authenticationBundle\Entity\User $idUser)
{
    $this->idUser[] = $idUser;

    return $this;
}

/**
 * Remove idUser
 *
 * @param \Procob\authenticationBundle\Entity\User $idUser
 */
public function removeIdUser(\Procob\authenticationBundle\Entity\User $idUser)
{
    $this->idUser->removeElement($idUser);
}

/**
 * Get idUser
 *
 * @return \Doctrine\Common\Collections\Collection 
 */
public function getIdUser()
{
    return $this->idUser;
}
}

How do I return the values of the three tables with the two entities created? I read the documentation but could not.

Answer 1

ManyToMany association is supposed to be used only for the relation between two entities without extra information about this relation.

If you want to store extra information in the relation create separate entity UserSystem and add ManyToOne association to both User and System entities.

Then you can use next dql to get all users with systems and its relations:

SELECT u, s, us
FROM   AppBundle:User u
JOIN   u.userSystems us
JOIN   us.system s