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.

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();
}