CODEWITHSUNDEEP
phpsymfonydoctrine-orminner-joinone-to-many
Alekc
Alekc

Reputation: 4770

Doctrine innerjoin on "one to many" relationship with querybuilder

I have following ER and i need to execute following query in Symfony2-Doctrine using Query Builder (join is used only when particular filter is requested)

SELECT *
FROM
  `session`
  INNER JOIN `goal` ON (`session`.`id` = `goal`.`session_id`)
WHERE
  `goal`.`name` = 'Background Dx' AND 
  `session`.`gsite_id` = '66361836'

ER

Goal declaration

/**
....
 *
 * @ORM\Table(name="goal")
 * @ORM\Entity
 */
class Goal
{
    ...
    /**
     * @var Session
     *
     * @ORM\ManyToOne(targetEntity="Session")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="session_id", referencedColumnName="id")
     * })
     */
    private $session;
    ...
}

Session class doesn't contain any reference to goal

By reading online documentation it seems that doctrine requires presence of column on entity Session for performing requested join. Is it really so, or there is another way?

My QueryBuilder

$rep = $this->em->getRepository(Session::repositoryName);
$qb = $rep->createQueryBuilder("s");

$qb->setMaxResults(50);


//site
if ($params->site != null){
    /** @var Gsite **/
    $site = $params->site;
    $qb->andWhere($qb->expr()->eq("s.site",":site"))->setParameter("site",$site);
}
if (isset($params->goalName)){
    ///SOMETHING
    ....
    ...
    ...
}

$query = $qb->getQuery();
$paginator = new Paginator($query);
return $paginator;

Upvotes: 5

Views: 19458

Answers (1)

Son Nguyen
Son Nguyen

Reputation: 401

You don't need reference from Session to Goal to perform a join. However, Doctrine won't know to map Goal data to Session object (so you just won't get it).

$qb->innerJoin('You\YourBundle\Entity\Goal', 'g', Expr\Join::WITH, 's.id = g.session')
->where('g.name = :goalName')->andWhere('s.gsiteId = :gsiteId')
->setParameter('goalName', 'Background Dx')->setParameter('gsiteId', '66361836');

Hope I understand your question correctly.

Upvotes: 14

Related Questions