We either use createQueryBuilder or createQuery to write queries in repository classes. See examples below. Note: The reason why we're using joins is because lazy load would run an additional query to select associated records in League table.


Example Entities


This is a one-to-many example.


class Country
{
protected $id;

/**
* @ORM\OneToMany(
* targetEntity="League",
* mappedBy="country",
* cascade={"persist", "remove"}
* )
*/
protected $league;
}

class League
{
protected $id;

/**
* @ORM\ManyToOne(
* targetEntity="Country",
* inversedBy="league"
* )
* @ORM\JoinColumn(
* name="country_id",
* referencedColumnName="id",
* onDelete="CASCADE",
* nullable=false
* )
*/
protected $country;
}

Example 1


public function findAll()
{
return
$this
->createQueryBuilder('c')
->select('c, l')
->leftJoin('c.league', 'l')
->orderBy('c.name', 'ASC')
->getQuery()
->getResult();
}

public function findAllDql()
{
return
$this
->getEntityManager()
->createQuery(
'SELECT c, l
FROM FootballFrontendBundle:Country c
LEFT JOIN c.league l
ORDER BY c.name ASC'
)
->getResult();
}

Example 2


public function findOneById($id)
{
return
$this
->createQueryBuilder('c')
->select('c, l')
->leftJoin('c.league', 'l')
->where('c.id = :id')
->setParameter('id', $id)
->getQuery()
->getSingleResult();
}

public function findOneById($id)
{
return
$this
->getEntityManager()
->createQuery(
'SELECT c, l
FROM FootballFrontendBundle:Country c
LEFT JOIN c.league l
WHERE c.id = :id'
)
->setParameters(['id' => $id])
->getSingleResult();
}