Symfony/Doctrine: Select multiple tables

Question

Im pretty new to Symfony and Doctrine and I can't find a solution to my problem.

I have a database table called transactional and one called customer. In the transactional table is a foreign key to the customer table. Now I want to get all the data from both tables. But the Customer fields are all set to null.

Here is the foreign key in the transactional php object:

transactional:

/**
 * @var \AppBundle\Entity\Customer
 *
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Customer")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="fk_customer", referencedColumnName="id")
 * })
 */
private $fkCustomer;

The Doctrine Query:

$em = $this->getDoctrine()->getManager();
$transactions = $em->getRepository('AppBundle:Transactional')->findAll();
dump($transactions);

The result:

0 => Transactional {#483 ▼
-id: 1
-date: DateTime @1510873200 {#493 ▶}
-fkCustomer: Customer {#566 ▼
  +__isInitialized__: false
  -id: 1
  -gender: null
  -firstname: null

Thank you very much for your time and help. =)

Answer 1

That is doctrines lazy loading.

Once you access the customer property of your Transactional object the related information will be loaded.

This isn't ideal however if you iterate over many transactional entries since every customer object will be loaded through a single query.

You can solve this either by setting the fetchMode to EAGER like:

/**
 * @var \AppBundle\Entity\Customer
 *
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Customer", fetch="EAGER")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="fk_customer", referencedColumnName="id")
 * })
 */
private $fkCustomer;

which should populate the customer data completely instead of using a proxy object.

Another way would be loading the transactional items through a custom repository method that explicitly joins the customer data. For example by creating a custom repository for Transactional and add a funciton like this:

public function load()
{
    $qb = $this->_em->createQueryBuilder();
    $qb->select('t, c')
        ->from('AppBundle:Transactional','t')
        ->join('t.fkCustomer', 'c');

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

how to create a custom repository can be found in the docs: https://symfony.com/doc/3.3/doctrine/repository.html

Answer 2

You have to set fetch type to eager:

Eager Type: Load the associated entity as well.

Lazy Type: Load the associated entity on need.

/**
 * @var \AppBundle\Entity\Customer
 *
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Customer",fetch="EAGER")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="fk_customer", referencedColumnName="id")
 * })
 */
private $fkCustomer;