Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine

Relation OrderBy & fetch=EXTRA_LAZY

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

You know what? On this page, we're missing the "created at" for each answer: I want to be able to see when each answer was posted. Let's fix that.

Head over to the template - show.html.twig - and, down here... right before the vote arrows, add a <small> tag and then {{ answer.createdAt }}. Of course, that will give us a DateTime object... and you can't just print a DateTime. But you can pipe it to the date() filter. Or in the last tutorial, we installed a library that allows us to say |ago.

... lines 1 - 4
{% block body %}
<div class="container">
... lines 7 - 54
<ul class="list-unstyled">
{% for answer in question.answers %}
<li class="mb-4">
<div class="row">
... lines 59 - 65
<div class="col-2 text-end">
<small>{{ answer.createdAt|ago }}</small>
... lines 68 - 89
</div>
</div>
</li>
{% endfor %}
</ul>
</div>
{% endblock %}

When we refresh now... oh! We get an error:

The Question object cannot be found by the @ParamConverter annotation.

That's a fancy way of saying that no Question for the slug in the URL could be found in the database. And that's because I reloaded my fixtures. Go to the homepage, refresh... and click into a fresh question. Actually, let me try a different one... I want something with several answers. Perfect. And each answer does display how long ago it was added.

Ordering $question->getAnswers() with ORM\OrderBy

But this highlights a small problem... or question: what order are these answers being returned from the database? Right now... there's no specific order. You can see that in the query for the answers: it just queries for all the answers where question_id = ? this question... but there's no ORDER BY.

At first, it seems like this is one of the downsides of using the convenience methods for a relationship like $question->getAnswers(): you don't have a lot of control over the results. But... that's not entirely true.

The easiest thing that you can control is how the answers are ordered. Go into the Question class and scroll up to the $answers property. To control the order add @ORM\OrderBy() and pass this an array with {"createdAt" = "DESC"}.

... lines 1 - 14
class Question
{
... lines 17 - 51
/**
* @ORM\OneToMany(targetEntity=Answer::class, mappedBy="question")
* @ORM\OrderBy({"createdAt" = "DESC"})
*/
private $answers;
... lines 57 - 177
}

That's it! Go back, refresh and... perfect! These are now ordered with the newest first!

Optimizing The Query to Count a Relation: EXTRA_LAZY

Let's learn another trick. On the homepage, we show the number of answers for each question. Well... kind of: they all say 6 because that number is still hardcoded. Let's fix that.

Open the template for this: templates/question/homepage.html.twig... and I'll search for "6". Here it is. Replace this with {{ question.answers|length }}

... lines 1 - 9
<div class="container">
... lines 11 - 15
<div class="row">
{% for question in questions %}
<div class="col-12 mb-3">
<div style="box-shadow: 2px 3px 9px 4px rgba(0,0,0,0.04);">
... lines 20 - 37
<a class="answer-link" href="{{ path('app_question_show', { slug: question.slug }) }}" style="color: #fff;">
<p class="q-display-response text-center p-3">
<i class="fa fa-magic magic-wand"></i> {{ question.answers|length}} answers
</p>
</a>
</div>
</div>
{% endfor %}
</div>
</div>
... lines 48 - 50

So we get the collection of answers and then count them. Simple enough! And if we try it... this works: two answers, six answers, eight answers.

But check out the web debug toolbar. Woh! We suddenly have a lot of queries. Click to open Doctrine's profiler. The first query is still for all of the question objects. But then, one-by-one it selects FROM answer WHERE question_id = ? a specific question. It does this for the first question, then it selects the answers for the next question... and the next and the next.

This is called the N+1 problem: We have 1 query that gives us all of the questions. Then, for each of the the N questions, when we ask for its answers, it makes another query. The total query count is the number of questions - N - plus 1 for the original.

We're going to talk more about the N+1 problem later and how to fix it. But there's kind of a bigger problem right now: we're querying for all of the answer data.... simply to count them! That's total overkill!

As soon as we access this answers property, Doctrine queries for all the data so that it can return all of the Answer objects. Normally, that's great - because we do want to use those Answer objects. But in this case... all we want to do is count them!

If you find yourself in this situation, there is a solution. In the Question class, at the end of the OneToMany(), pass a new option called fetch="" set to EXTRA_LAZY.

... lines 1 - 14
class Question
{
... lines 17 - 51
/**
* @ORM\OneToMany(targetEntity=Answer::class, mappedBy="question", fetch="EXTRA_LAZY")
* @ORM\OrderBy({"createdAt" = "DESC"})
*/
private $answers;
... lines 57 - 177
}

Watch what happens. Right now we have 21 queries. When we refresh, we still have 21 queries. But open up the profiler. The first query is still the same. But every query after just selects COUNT() FROM answer! Instead of querying for all of the answer data, it only counts them!

This is what fetch="EXTRA_LAZY" gets you. If Doctrine determines that you're accessing a relation... but you're only counting that relation - not actually trying to use its data - then it will create a "count" query instead of grabbing all the data.

That's awesome! So awesome that you might be wondering: why isn't this the default behavior? If I'm counting the relation, why would we ever want Doctrine to query for all of the data?

Well... EXTRA_LAZY isn't always a good thing. Go to a question show page. Having the EXTRA_LAZY actually causes an extra query here. Before that change, this page required 2 queries. Now it has 3. Check them out. First, it selects the question data. Then it counts the answers. And then it re-does that query to grab all the data for the answers. That second COUNT query is new... and, in theory, shouldn't be needed.

The problem is the order of the code in the template. You can see this in show.html.twig: before we loop over the answers and use their data, we first count them. So at this moment Doctrine says:

Hey! You want to count the answers! I'll make a quick COUNT query for that.

Then, a millisecond later, we loop over all the answers... and so we need their data anyways. This causes Doctrine to make the full query.

If we reversed the order of this code - where we loop and use the data first - Doctrine would avoid the extra COUNT query because it would already know how many answers it has because it just queried for their data.

All of this is probably not too important and I'm going to leave it. In general, don't overly worry about optimizing. In the real world, I use Blackfire on production to find what my real performance issues are.

Next: in addition to changing the order of the answers when we call $question->getAnswers(), we can also filter this collection to, for example, only return approved answers. Let's get that set up next.

Leave a comment!

6
Login or Register to join the conversation
Trendency Avatar
Trendency Avatar Trendency | posted 1 month ago

This Code write you
class AppFixtures extends Fixture
{
public function load(ObjectManager $manager)
{
AnswerFactory::new(function() use ($questions) {
return [
'question' => $questions[array_rand($questions)]
];
})->needsApproval()->many(20)->create();
}
}

But a few episodes ago u use this shortcut
class AppFixtures
AnswerFactory::createMany(100);

this shortcutis good for this epidose I think
class AppFixtures
AnswerFactory::createMany(100);
AnswerFactory::new()->needsApproval()->many(20)->create();

Cheers!

Reply
Sebastian O. Avatar
Sebastian O. Avatar Sebastian O. | posted 6 months ago

As I understood this chapter, TWIG triggers SQL-Queries, in other word, TWIG initiates additional DB-connections. Really? Until now I thought, only the controller (& services) triggers the SQL-queries via Doctrine and passes the results to TWIG via the return-array, so TWIG just could use what the controller already produced. Now which is correct?

Reply

Hey Planschkuh1992,

Yes, that's right. With ORM thing a bit more complicated, because you can query from the DB for example a user object, then pass it to the template, where you will call "user.getOrders()" and iterate over user's orders to list them. Doctrine may not load those orders initially for performance reason when you fetch the object from the DB, because probably you will never need that data. But on the explicit request of those data that happened in the template, additional queries might be sent to the DB to fetch that extra information. And it's kinda OK, because first of all, that's handled by third-party logic of that ORM, it's not about you sending a DB query directly from the template, right? it's happening behind the scene. But if you got access to the DB connection in the template and wrote a query that will hit the DB from inside the template - it would be a bad practice for sure.

That's just one example. I hope it clarify things for you.

Cheers!

1 Reply
davidmintz Avatar
davidmintz Avatar davidmintz | posted 8 months ago

are we just going to live with the N+1 problem? ....oops sorry my bad. I see we will get to that in Chapter 14.

Reply

Haha, no problem. And yes, often I DO just live with the N+1 problem anyways... Blackfire is great for showing me when that's *actually* a problem :).

Cheers!

Reply
davidmintz Avatar

often I DO just live with the N+1 problem

What?? I am a bit too obsessive-compulsive for that :-) seriously though --- I have created an ambitious Doctrine project and it works brilliantly, but there were a few spots where I never did figure out how to cut the queries down to as few as I believed they should be, and a lot of hoop-jumping to fix N+1. Part of my motivation for being here is to learn Doctrine better -- iow to get more inDoctrinated.

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

This tutorial also works great for Symfony 6!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.4.1 || ^8.0.0",
        "ext-ctype": "*",
        "ext-iconv": "*",
        "babdev/pagerfanta-bundle": "^3.3", // v3.3.0
        "composer/package-versions-deprecated": "^1.11", // 1.11.99.3
        "doctrine/doctrine-bundle": "^2.1", // 2.4.2
        "doctrine/doctrine-migrations-bundle": "^3.0", // 3.1.1
        "doctrine/orm": "^2.7", // 2.9.5
        "knplabs/knp-markdown-bundle": "^1.8", // 1.9.0
        "knplabs/knp-time-bundle": "^1.11", // v1.16.1
        "pagerfanta/doctrine-orm-adapter": "^3.3", // v3.3.0
        "pagerfanta/twig": "^3.3", // v3.3.0
        "sensio/framework-extra-bundle": "^6.0", // v6.2.1
        "stof/doctrine-extensions-bundle": "^1.4", // v1.6.0
        "symfony/asset": "5.3.*", // v5.3.4
        "symfony/console": "5.3.*", // v5.3.7
        "symfony/dotenv": "5.3.*", // v5.3.7
        "symfony/flex": "^1.3.1", // v1.17.5
        "symfony/framework-bundle": "5.3.*", // v5.3.7
        "symfony/monolog-bundle": "^3.0", // v3.7.0
        "symfony/runtime": "5.3.*", // v5.3.4
        "symfony/stopwatch": "5.3.*", // v5.3.4
        "symfony/twig-bundle": "5.3.*", // v5.3.4
        "symfony/validator": "5.3.*", // v5.3.14
        "symfony/webpack-encore-bundle": "^1.7", // v1.12.0
        "symfony/yaml": "5.3.*", // v5.3.6
        "twig/extra-bundle": "^2.12|^3.0", // v3.3.1
        "twig/string-extra": "^3.3", // v3.3.1
        "twig/twig": "^2.12|^3.0" // v3.3.2
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.3", // 3.4.0
        "symfony/debug-bundle": "5.3.*", // v5.3.4
        "symfony/maker-bundle": "^1.15", // v1.33.0
        "symfony/var-dumper": "5.3.*", // v5.3.7
        "symfony/web-profiler-bundle": "5.3.*", // v5.3.5
        "zenstruck/foundry": "^1.1" // v1.13.1
    }
}