Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

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
}
}
....
}