Select fields from many to many relationship between 3 tables in Doctrine and ZF2 with query builder

Question

I have User Entity:

class User{
...

/**
 * @var Module $modules
 * @ORM\ManyToMany(targetEntity="Adm\Entity\Module", inversedBy="users", cascade={"persist"})
 * @ORM\JoinTable(
 *  name="user_module",
 *  joinColumns={@ORM\JoinColumn(name="module_id", referencedColumnName="id")},
 *  inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}
 * )
 */
protected $modules;

public function __construct() {
    $this->modules = new \Doctrine\Common\Collections\ArrayCollection();
}
...
}

And Module Entity:

class Module{
...
/**
* @ORM\ManyToMany(targetEntity="Adm\Entity\User", mappedBy="modules")
*/
protected $users;
...
}

I have three tables: user, module and user_module(relation between user and module, no extra fields). I'm trying to build a query to get the user (for $id) and modules related to him, which relations are in user_module table. This is what i did:

    public function getUser($id){
        $qb = $this->getEm()->createQueryBuilder()
        ->select('u', 'm')
        ->from('Adm\Entity\User', 'u')
        ->join('u.modules', 'm')
        ->where('u.id = ?1')
        ->setParameter(1, $id)
        ->setMaxResults(1);
        $result = $qb->getQuery()->getResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
        return $result;
    }

But is not bringing the related modules with the user, it's only bringing 1 result from module table, which has the same id as user. Please help me to build this query. Thanks!

Answer 1

I realized i was inverting the join column statement:

*  joinColumns={@ORM\JoinColumn(name="module_id", referencedColumnName="id")},
*  inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")}

It has to be:

*  joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
*  inverseJoinColumns={@ORM\JoinColumn(name="module_id", referencedColumnName="id")}

And also, i had to remove this statement in my function, thanks to @Garry to point it to me:

->setMaxResults(1)

Now it is working as expected.