Writing doctrine query builder without relations between entities

14/05/2017 - DOCTRINE, SYMFONY

In this example we are going to create a query builder to join two entities that don't have relationship/mapping between each other. Instead of mapping, one of the entity will have just the foreign key to another. Example below technically a one-to-many (Order->Product) relation but we won't add the ORM mapping.


Order


/**
 * @ORM\Entity
 * @ORM\Table(name="order")
 */
class Order
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    ....
}

Product


/**
 * @ORM\Entity
 * @ORM\Table(name="product")
 */
class Product
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var int
     *
     * @ORM\Column(name="order_id", nullable=true, type="integer")
     */
    private $orderId;

    ....
}

OrderRepository


class OrderRepository extends EntityRepository
{
    public function findOneByIdAndAllItsProducts($orderId)
    {
        return $this->createQueryBuilder('or')
            ->select('or, pr')
            ->join('MyShopBundle:Product', 'pr', 'WITH', 'pr.orderId = or.id')
            ->where('or.id = :id')
            ->setParameter('id', $orderId)
            ->getQuery()
            ->getResult();
    }
}

OrderService


class OrderService
{
    ....

    public function getOne($orderId)
    {
        $result = $this->orderRepository->findOneByIdAndAllItsProducts($orderId);
        if (!$result) {
            // Throw an exception
        }

        // Index 0 is always the Order object and the rest are Product objects
        $orderEntity = $result[0];
        unset($result[0]);

        foreach ($result as $item) {
            echo get_class($item).PHP_EOL; // This will always be the Product object
        }
    }
    ....
}