Symfony join entities in one-to-many relationship

Question

I am working on a Symfony project recording sales as relating to stock.

My reasoning:

• one sale item is associated to one stock item
• one stock item can be associated to multiple sale items

As a result, I setup a one-to-many sale-to-stock relationship as show in the following code snippets:

class Sale
{

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var float
     *
     * @ORM\Column(name="cost", type="float")
     */
    private $cost;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date", type="datetime")
     */
    private $date;

    /**
     * @ORM\ManyToOne(targetEntity="iCerge\Salesdeck\StockBundle\Entity\Stock", inversedBy="sales")
     * @ORM\JoinColumn(name="sid", referencedColumnName="id")
     */
    protected $stock;
...

... and ...

class Stock
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="created", type="datetime")
     */
    private $created;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="updated", type="datetime")
     */
    private $updated;

    /**
     * @ORM\OneToMany(targetEntity="iCerge\Salesdeck\SalesBundle\Entity\Sale", mappedBy="stock")
     */
    protected $sales;
...

NOW, if my code for implementing a one-to-many relationship is correct, I am trying to load a sale object with it's associated stock data in one query using the following code:

public function fetchSale($sid){
        $query = $this->createQueryBuilder('s')
            ->leftjoin('s.stock', 't')
            ->where('s.id = :sid')
            ->setParameter('sid', $sid)
            ->getQuery();
        return $query->getSingleResult();
    }

The fetchSale function is from my projects SaleRepository.php class. The leftjoin part of the query is what I hoped would successfully fetch the related stock information but I just get no output ([stock:protected]) as is shown below:

    myprog\SalesProj\SalesBundle\Entity\Sale Object
(
    [id:myprog\SalesProj\SalesBundle\Entity\Sale:private] => 50
    [cost:myprog\SalesProj\SalesBundle\Entity\Sale:private] => 4.99
    [date:myprog\SalesProj\SalesBundle\Entity\Sale:private] => DateTime Object
        (
            [date] => 2015-04-18 17:12:00
            [timezone_type] => 3
            [timezone] => UTC
        )

    [stock:protected] => 
    [count:myprog\SalesProj\SalesBundle\Entity\Sale:private] => 5
)

How I can successfully fetch a sales' related stock data in the same query?

Answer 1

You can use Doctrine's eager loading feature.

1. Always load associated object:

If you always want to fetch the stock object when loading the sale object, you can update your entity definition (see docs for @ManyToOne) by adding a fetch="EAGER" to the @ManyToOne definition:

/**
 * @ORM\ManyToOne(targetEntity="iCerge\Salesdeck\StockBundle\Entity\Stock", inversedBy="sales", fetch="EAGER")
 * @ORM\JoinColumn(name="sid", referencedColumnName="id")
 */
protected $stock;

Doctrine will then take care of loading all required objects in as few queries as possible.

2. Sometimes load associated object:

If you want to load the associated object only in some queries and not by default, according to the manual you can also tell Doctrine to use eager loading on a specific query. In your case, this may look like:

public function fetchSale($sid){
    $query = $this->createQueryBuilder('s')
        ->where('s.id = :sid')
        ->setParameter('sid', $sid)
        ->getQuery();
    $query->setFetchMode("FQCN\Sale", "stock", \Doctrine\ORM\Mapping\ClassMetadata::FETCH_EAGER);
    return $query->getSingleResult();
}

Answer 2

Doctrine is lazy-loading by default, so it's possible that $stock hasn't been initialized and the dump is not showing it. Try dumping $sale->getStock(). That tells Doctrine to go fetch it.

You can also force the loading of the Stock by selecting it:

public function fetchSale($sid){
    $query = $this->createQueryBuilder('s')
        ->leftjoin('s.stock', 't')
        ->where('s.id = :sid')
        ->setParameter('sid', $sid)
        ->select('s', 't')
        ->getQuery();
    return $query->getSingleResult();
}

By the way, fetchSale($sid) as it is now is the same as calling:

$entityManager->getRepository('SalesBundle:Sale')->find($sid);