Doctrine hydrator overwrites one related entity by another

Question

I need to get array of orders, with first and last OrderItem in it. Table order_item:

id	order_id	status
1	1	0
2	1	1
3	1	0
4	1	1
5	1	0

Order entity

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use JMS\Serializer\Annotation as Serializer;

/**
 * Order
 *
 * @ORM\Entity
 * @ORM\Table(name="orders")
 */
class Order
{
    /**
     * @var int
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @Serializer\Groups(groups="order_list")
     */
    private $id;
    
    /**
     * @var string
     * @ORM\Column(name="title", type="string", nullable=false)
     * @Serializer\Groups(groups={"order_list"})
     */
    private $title;
    
    /**
     * @var Collection
     * @ORM\OneToOne(targetEntity="OrderItem", mappedBy="orderFirst")
     * @Serializer\Groups(groups={"order_list"})
     */
    private $orderItemFirst;
    
    /**
     * @var Collection
     * @ORM\OneToOne(targetEntity="OrderItem", mappedBy="orderLast")
     * @Serializer\Groups(groups={"order_list"})
     */
    private $orderItemLast;
    
    /**
     * @var Collection|OrderItem[]
     * @ORM\OneToMany(targetEntity="OrderItem", mappedBy="order")
     * @Serializer\Groups(groups={"order_list"})
     */
    private $items;

    
    public function __construct()
    {
        $this->items = new ArrayCollection();
    }
    
    // getters and setters ...
}

OrderItem entity

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use JMS\Serializer\Annotation as Serializer;

/**
 * Order
 *
 * @ORM\Entity
 * @ORM\Table(name="order_item")
 */
class OrderItem
{
    /**
     * @var int
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @Serializer\Groups(groups="order_list")
     */
    private $id;
    
    /**
     * @var Order|null
     * @ORM\OneToOne(targetEntity="Order", inversedBy="orderItemFirst")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="order_id", referencedColumnName="id")
     * })
     */
    private $orderFirst;
    
    /**
     * @var Order|null
     * @ORM\OneToOne(targetEntity="Order", inversedBy="orderItemLast")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="order_id", referencedColumnName="id")
     * })
     */
    private $orderLast;
    
    /**
     * @var Order|null
     * @ORM\ManyToOne(targetEntity="Order", inversedBy="items")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="order_id", referencedColumnName="id")
     * })
     */
    private $order;
    
    /**
     * @var bool
     * @ORM\Column(type="boolean", nullable=false)
     * @Serializer\Groups(groups="order_list")
     */
    private $status;
    
    
    // getters and setters...
}

Code in service

    $repository = $em->getRepository(\App\Entity\Order::class);
    $qb = $repository->createQueryBuilder('t');
    $qb
        ->leftJoin('t.orderItemFirst', 'oi', \Doctrine\ORM\Query\Expr\Join::WITH, 'oi.status = :status')
        ->leftJoin('t.orderItemFirst', 'oi2', \Doctrine\ORM\Query\Expr\Join::WITH, 'oi2.status = :status AND oi2.id > oi.id')
        ->leftJoin('t.orderItemLast', 'oi3', \Doctrine\ORM\Query\Expr\Join::WITH, 'oi3.status = :status')
        ->leftJoin('t.orderItemLast', 'oi4', \Doctrine\ORM\Query\Expr\Join::WITH, 'oi4.status = :status AND oi4.id < oi3.id')
        ->setParameter('status', true)
        ->addSelect('oi')
        ->addSelect('oi3')
        ->where('oi2.id IS NULL')
        ->andWhere('oi4.id IS NULL')
        ->groupBy('t.id')
        ;
    
    $query = $qb->getQuery();
    $query->setHint(\Doctrine\ORM\Query::HINT_REFRESH, true);
    
    /*
     * This returns
     * "orderItemFirst": {"id": 4}
     * "orderItemLast": {"id": 4}
     */
    $result = $query->getResult();

But getArrayResult() returns correct data:

"orderItemFirst": {
    "id": 2,
    "status": true
},
"orderItemLast": {
    "id": 4,
    "status": true
}

And even if I call getArrayResult() at first, getResult() starts to hydrate correctly. What a strange behavior? Can it be done without double executing query?

You can't use the same join column order_id for several fields (orderFirst, orderLast, order), that's the reason why hydrating doesn't work correctly. If you'd like for an order to have its first and last items **through doctrine mapping**, you need to store their ids in the orders table explicitely (i.e. to have fields like order_item_first_id and order_item_last_id in the orders table). — xtx, Aug 10 '21 at 02:27
Another option is just to create 2 methods in the Order class, like getFirstOrderItem and getLastOrderItem, which would pick the first and the last item from the items collection — xtx, Aug 10 '21 at 02:40

Doctrine hydrator overwrites one related entity by another

0 Answers0