How to avoid dangerous doctrine lazy association loading in symfony

17/05/2015 - DOCTRINE, SYMFONY

Lazy loading can be "bad" if you don't know what you're doing. By default, lazy loading is enabled in doctrine so we should take care of our own queries. The best tip I can give you is, create your own repository class to handle all the queries by writing DQLs if associations are involved. For example; a Country can have zero or many League which is a one-to-many relationship. Now checkout the example below.


Entities with one-to-many association


# Country entity

/**
 * @ORM\Entity
 * @ORM\Table(name="country")
 */
class Country
{
    protected $id;
    protected $code;
    protected $name;
    protected $createdAt;
    protected $updatedAt;

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

# League entity

/**
 * @ORM\Entity
 * @ORM\Table(name="league")
 */
class League
{
    protected $id;
    protected $name;
    protected $createdAt;
    protected $updatedAt;

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

Dummy data


mysql> SELECT * FROM country;
+----+----------------+---------------------+------------+------+
| id | name           | created_at          | updated_at | code |
+----+----------------+---------------------+------------+------+
| 28 | Germany        | 2015-05-16 19:59:00 | NULL       | DE   |
| 29 | Spain          | 2015-05-16 19:59:00 | NULL       | ES   |
| 30 | Turkey         | 2015-05-16 19:59:00 | NULL       | TR   |
| 31 | United Kingdom | 2015-05-16 21:39:25 | NULL       | GB   |
+----+----------------+---------------------+------------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM league;
+----+------------+-------------------+---------------------+------------+
| id | country_id | name              | created_at          | updated_at |
+----+------------+-------------------+---------------------+------------+
|  6 |         28 | Bundesliga        | 2015-05-16 19:59:00 | NULL       |
|  7 |         29 | Primera División  | 2015-05-16 19:59:00 | NULL       |
|  8 |         29 | Segunda División  | 2015-05-16 19:59:00 | NULL       |
|  9 |         30 | Süper Lig         | 2015-05-16 19:59:00 | NULL       |
| 10 |         30 | TFF 1. Lig        | 2015-05-16 19:59:00 | NULL       |
| 11 |         31 | Premier League    | 2015-05-16 19:59:00 | NULL       |
+----+------------+-------------------+---------------------+------------+
6 rows in set (0.00 sec)

Country - bad way


The reason why we consider it as "bad" because, we're going to access associated records with lazy load as well of parent entity. We're using built-in findAll() doctrine method. Uncontrolled lazy load will run additional queries in $c->getLeague()->first()->getName() and {{ country.getLeague.first.name }} line. As a result, this request would run total of 5 queries. 1 for selecting all countries and 1 for each countries to get their first associated league data.


# Country controller

class CountryController
{
    public function listAction()
    {
        $repo = $this->getDoctrine()->getRepository('FootballFrontendBundle:Country');

        $country = $repo->findAll();

//        foreach ($country as $c) {
//            $id = $c->getId();
//            $code = $c->getCode();
//            // ....
//            $league = $c->getLeague()->first()->getName();
//        }

        return $this->render(
            'FootballFrontendBundle:Country:list.html.twig', ['countryArray' => $country]
        );
    }
}

# FootballFrontendBundle:Country:list.html.twig

{% if countryArray is defined and countryArray|length != 0 %}
    <table border="1px">
        <tr>
            <td>#</td>
            <td>ID</td>
            <td>CODE</td>
            <td>NAME</td>
            <td>CREATED</td>
            <td>UPDATED</td>
            <td>LEAGUE</td>
        </tr>
        {% for country in countryArray %}
            <tr>
                <td class="head">{{ loop.index }}</td>
                <td>{{ country.id }}</td>
                <td>{{ country.code }}</td>
                <td>{{ country.name }}</td>
                <td>{{ country.createdAt|date('d/m/Y H:i:s') }}</td>
                <td>{{ country.updatedAt is not null ? country.updatedAt|date('d/m/Y H:i:s') : '' }}</td>
                <td>{{ country.getLeague.first.name }}</td>
            </tr>
        {% endfor %}
    </table>
{% else %}
    No country found in database!
{% endif %}

Country - good way


We're going to create a repository class to define our own findAll() method to select associated leagues as well. Process above would now run only 1 query. The output below would be the same for both of these examples.


# Country repository

class CountryRepository extends EntityRepository
{
    public function findAll()
    {
        return
            $this->createQueryBuilder('c')
            ->select('c, l')
            ->leftJoin('c.league', 'l')
            ->getQuery()
            ->getResult();
    }
}

Output



League - bad way


The comments above apply to this part as well. Uncontrolled lazy load will run additional queries in $l->getCountry()->getName() and {{ league.country.name }} line. As a result, this request would run total of 5 queries. 1 for selecting all leagues and 1 for each leagues to get their first associated country data.


# League controller

class LeagueController
{
    public function listAction()
    {
        $repo = $this->getDoctrine()->getRepository('FootballFrontendBundle:League');

        $league = $repo->findAll();

//        foreach ($league as $l) {
//            $id = $l->getId();
//            $name = $l->getName();
//            // ...
//            $countryName = $l->getCountry()->getName();
//        }

        return $this->render(
            'FootballFrontendBundle:League:list.html.twig', ['leagueArray' => $league]
        );
    }
}

# FootballFrontendBundle:League:list.html.twig

{% if leagueArray is defined and leagueArray|length != 0 %}
    <table border="1px">
        <tr>
            <td>#</td>
            <td>ID</td>
            <td>NAME</td>
            <td>CREATED</td>
            <td>UPDATED</td>
            <td>COUNTRY</td>
        </tr>
        {% for league in countryArray %}
            <tr>
                <td class="head">{{ loop.index }}</td>
                <td>{{ league.id }}</td>
                <td>{{ league.name }}</td>
                <td>{{ league.createdAt|date('d/m/Y H:i:s') }}</td>
                <td>{{ league.updatedAt is not null ? league.updatedAt|date('d/m/Y H:i:s') : '' }}</td>
                <td>{{ league.country.name }}</td>
            </tr>
        {% endfor %}
    </table>
{% else %}
    No league found in database!
{% endif %}

League - good way


We're going to create a repository class to define our own findAll() method to select associated countries as well. Process above would now run only 1 query. The output below would be the same for both of these examples.


# League repository

class LeagueRepository extends EntityRepository
{
    public function findAll()
    {
        return
            $this->createQueryBuilder('l')
                ->select('l, c')
                ->leftJoin('l.country', 'c')
                ->getQuery()
                ->getResult();
    }
}

Output