Join between ManyToMany properties at Doctrine2 entities

Question

I have this tables relationship:

This is how my entities for messages and emails looks like (I've just added the important info):

class Message
{
    /**
     * @var Brand
     *
     * @ORM\ManyToOne(targetEntity="Brand")
     * @ORM\JoinColumn(name="brands_id", referencedColumnName="id")
     */
    protected $brand;

    ...

}


class Email
{
    /**
     * @ORM\ManyToMany(targetEntity="Message", inversedBy="messageXrefMail", cascade={"persist", "remove"})
     * @ORM\JoinTable(name="emails_messages_xref",
     *      joinColumns={@ORM\JoinColumn(name="emails_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="messages_id", referencedColumnName="id")}
     * )
     */
    protected $emailsMessageXref;

    ...
}

I need to get the brands related to email but I don't know how to do it in this scenario. The are a few tables included on the main query and I got this:

public function findEmailValues($eid)
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('e.id', 'r.id')
        ->from('PDOneBundle:Email', 'e')
        ->leftJoin('PDOneBundle:Representative', 'r', \Doctrine\ORM\Query\Expr\Join::WITH, 'e.rep = r.id')
        ->leftJoin('PDOneBundle:Territory', 't', \Doctrine\ORM\Query\Expr\Join::WITH, 'r.territory = t.id')
        ->leftJoin('PDOneBundle:Target', 'tg', \Doctrine\ORM\Query\Expr\Join::WITH, 't.id = tg.territory')
        ->where('e.id = :eid')
        ->setParameter('eid', $eid);

    return $qb->getQuery()->getSingleResult();
}

But I don't know where to go from there. Can I get some help building the DQL?

Answer 1

This should work:

    $qb->select('e.id', 'r.id', 'br')
        ->from('PDOneBundle:Email', 'e')
        ->leftJoin('PDOneBundle:Representative', 'r', \Doctrine\ORM\Query\Expr\Join::WITH, 'e.rep = r.id')
        ->leftJoin('PDOneBundle:Territory', 't', \Doctrine\ORM\Query\Expr\Join::WITH, 'r.territory = t.id')
        ->leftJoin('PDOneBundle:Target', 'tg', \Doctrine\ORM\Query\Expr\Join::WITH, 't.id = tg.territory')
        ->leftJoin('e.emailsMessageXref', 'me') // many to many on email -> messages
        ->leftJoin('me.brand', 'br') // many to one on message -> brand
        ->where('e.id = :eid')
        ->setParameter('eid', $eid);