CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdoctrine
Asq
Asq

Reputation: 105

How to join entites like they are tables in Doctrine 2?

The documentation on how to retrieve joined entites as one object is very sparse, and most Stack Overflow questions on the subject are many years old.

I have a symfony project up and running with a database schema fully mapped in Doctrine. In my controllers I am able to run these two queries one after the other and they work fine.

$page = $this->getDoctrine()
    ->getRepository('PageBundle:SitePages')
    ->findByprodpageid($id);

$matrices = $this->getDoctrine()
    ->getRepository('PageBundle:SiteMatrices')
    ->findByprodpageid($id);

however both of them contain the attribute prodpageid and I would like to join the two entities on this column and receive one object containing all column values from both tables.

I am building this on top of an existing database structure so anything to do with changing the database structure etc is out of the question.

I have added annotations in my entities to specify which columns should be joined, in a ManyToOne relationship. But how do I activate that relation and receive the joined object?

Thanks for any info on the subject.

EDIT: Here are my relationships from the entities

//Entities/SitePages

/**
 * @var integer
 *
 * @ORM\Column(name="ProdPageID", type="smallint")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 *
 * @ORM\OneToMany(targetEntity="SiteMatrices")
 * @ORM\JoinColumn(name="prodpageid", referencedColumnName="prodpageid")
 */
private $prodpageid;

//Entities/SiteMatrices

/**
 * @var integer
 *
 * @ORM\Column(name="ProdPageID", type="smallint", nullable=false)
 * @ORM\ManyToOne(targetEntity="SitePages")
 * @ORM\JoinColumn(name="prodpageid", referencedColumnName="prodpageid")
 *
 */
private $prodpageid;

Upvotes: 1

Views: 101

Answers (1)

Mohamad
Mohamad

Reputation: 1065

You are saying that a Page has many Matrices. I will make some changes by your permission in mapping annotations: 

/**
 * Entities/SitePages
 *
 * @var integer
 *
 * @ORM\Column(name="ProdPageID", type="smallint")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 *
 * @ORM\OneToMany(targetEntity="SiteMatrices")
 * @ORM\JoinColumn(name="prodpageid", referencedColumnName="prodpageid")
 */
private $matrices;

/**
* @return ArrayCollection
*/
public function getMatrices(){
   return $this->matrices;
}

/**
* @param Entities/SiteMatrices[]
* @return $this
*/
public function setMatrices($matrices){
    $this->matrices = $matrices;
    return $this;
}

and 

/**
 * Entities/SiteMatrices
 *
 * @var integer
 *
 * @ORM\Column(name="ProdPageID", type="smallint", nullable=false)
 * @ORM\ManyToOne(targetEntity="SitePages")
 * @ORM\JoinColumn(name="prodpageid", referencedColumnName="prodpageid")
 *
 */
private $page;

/**
* @return Entities/SitePages
*/
public function getPage(){
   return $this->page;
}

/**
* @param Entities/SitePages
* @return $this
*/
public function setPage($page){
    $this->page = $page;
    return $this;
}

Now if you query the Pages with this DQL: 

$pages = $this->getDoctrine()
   ->getRepository('PageBundle:SitePages')
   ->findByprodpageid($id);

Then you could get each page's matrices simply by traversing on matrices association: 

foreach($pages as $page){
    $matrices = $page->getMatrices(); // will give you an ArrayCollection of all matrices objects joined by prodpageid to this page.
}

Hope I did not get you wrong and it helps.

Upvotes: 3

Related Questions