OrderBy & fetch EXTRA_LAZY

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

It's great that each article now has real, dynamic comments at the bottom. But... something isn't right: the comments are in a, kind of, random order. Actually, they're just printing out in whatever order they were added to the database. But, that's silly! We need to print the newest comments on top, the oldest at the bottom.

How can we do this? Check out the template. Hmm, all we're doing is calling article.comments:

            
            87 lines
            
            templates/article/show.html.twig
        
                                                    ... lines 1 - 6
                            
            <div class="container">
        
                <div class="row">
        
                    <div class="col-sm-12">
        
                        <div class="show-article-container p-3 mt-4">
        
                                                    ... lines 11 - 39
                            
                            <div class="row">
        
                                <div class="col-sm-12">
        
                                                    ... lines 42 - 57
                            
                                    {% for comment in article.comments %}
        
                                    <div class="row">
        
                                        <div class="col-sm-12">
        
                                            <img class="comment-img rounded-circle" src="{{ asset('images/alien-profile.png') }}">
        
                                            <div class="comment-container d-inline-block pl-3 align-top">
        
                                                <span class="commenter-name">{{ comment.authorName }}</span>
        
                                                <small>about {{ comment.createdAt|ago }}</small>
        
                                                <br>
        
                                                <span class="comment"> {{ comment.content }}</span>
        
                                                <p><a href="#">Reply</a></p>
        
                                            </div>
        
                                        </div>
        
                                    </div>
        
                                    {% endfor %}
        
                                </div>
        
                            </div>
        
                        </div>
        
                    </div>
        
                </div>
        
            </div>
        
                                                    ... lines 79 - 87

Which is the getComments() method on Article:

            
            202 lines
            
            src/Entity/Article.php
        
                                                    ... lines 1 - 13
                            
            class Article
        
            {
        
                                                    ... lines 16 - 170
                            
                /**
        
                 * @return Collection|Comment[]
        
                 */
        
                public function getComments(): Collection
        
                {
        
                    return $this->comments;
        
                }
        
                                                    ... lines 178 - 200
                            
            }

The great thing about these relationship shortcut methods is that.... they're easy! The downside is that you don't have a lot of control over what's returned, like, the order of this article's comments.

Well... that's not entirely true. We can control the order, and I'll show you how. Actually, we can control a lot of things - but more on that later.

@ORM\OrderBy()

Scroll all the way to the top and find the comments property:

            
            202 lines
            
            src/Entity/Article.php
        
                                                    ... lines 1 - 13
                            
            class Article
        
            {
        
                                                    ... lines 16 - 60
                            
                /**
        
                 * @ORM\OneToMany(targetEntity="App\Entity\Comment", mappedBy="article")
        
                 */
        
                private $comments;
        
                                                    ... lines 65 - 200
                            
            }

Add a new annotation: @ORM\OrderBy() with {"createdAt" = "DESC"}:

            
            203 lines
            
            src/Entity/Article.php
        
                                                    ... lines 1 - 13
                            
            class Article
        
            {
        
                                                    ... lines 16 - 60
                            
                /**
        
                 * @ORM\OneToMany(targetEntity="App\Entity\Comment", mappedBy="article")
        
                 * @ORM\OrderBy({"createdAt" = "DESC"})
        
                 */
        
                private $comments;
        
                                                    ... lines 66 - 201
                            
            }

That's it! Move over and, refresh! Brilliant! The newest comments are on top. This actually changed how Doctrine queries for the related comments.

Oh, and I want to mention two quick things about the syntax for annotations. First... well... the syntax can sometimes be confusing - where to put curly braces, equal sign etc. Don't sweat it: I still sometimes need to look up the correct syntax in different situations.

Second, for better or worse, annotations only support double quotes. Yep, you simply cannot use single quotes. It just won't work.

Fetch EXTRA_LAZY

I want to show you one other trick. Go back to the homepage. It would be really nice to list the number of comments for each article. No problem! Open homepage.html.twig. Then, inside the articles loop, right after the title, add a <small> tag, a set of parentheses, and use {{ article.comments|length }} and then the word "comments":

            
            59 lines
            
            templates/article/homepage.html.twig
        
                                                    ... lines 1 - 2
                            
            {% block body %}
        
                <div class="container">
        
                    <div class="row">
        
                        <!-- Article List -->
        
                        <div class="col-sm-12 col-md-8">
        
                                                    ... lines 10 - 18
                            
                            <!-- Supporting Articles -->
        
                            {% for article in articles %}
        
                            <div class="article-container my-1">
        
                                <a href="{{ path('article_show', {slug: article.slug}) }}">
        
                                                    ... line 24
                            
                                    <div class="article-title d-inline-block pl-3 align-middle">
        
                                                    ... line 26
                            
                                        <small>({{ article.comments|length }} comments)</small>
        
                                                    ... lines 28 - 30
                            
                                    </div>
        
                                </a>
        
                            </div>
        
                            {% endfor %}
        
                        </div>
        
                                                    ... lines 36 - 55
                            
                    </div>
        
                </div>
        
            {% endblock %}

I love it! Refresh the homepage. It works effortlessly! But... check out the queries down here on the web debug toolbar. If you click into it, there are suddenly 6 queries! The first query is what we expect: it finds all published articles.

The second query selects all of the comments for an article whose id is 176. The next is an identical query for article 177. As we loop over the articles, each time we call getComments(), at that moment, Doctrine fetches all of the comments for that specific Article. Then, it counts them.

This is a classic, potential performance issue with ORM's like Doctrine. It's called the N+1 problem. And, we'll talk about it later. But, it's basically that the cool lazy-loading of relationships can lead to an extra query per row. And this may cause performance issues.

But, forget about that for now, because, there's a simpler performance problem. We're querying for all of the comments for each article... simply to count them! That's insane!

This is the default behavior of Doctrine: as soon as you call getComments() and use that data, it makes a query at that moment to get all of the comment data, even if you eventually only need to count that data.

But, we can control this. In Article, at the end of the OneToMany annotation, add fetch="EXTRA_LAZY":

            
            203 lines
            
            src/Entity/Article.php
        
                                                    ... lines 1 - 13
                            
            class Article
        
            {
        
                                                    ... lines 16 - 60
                            
                /**
        
                 * @ORM\OneToMany(targetEntity="App\Entity\Comment", mappedBy="article", fetch="EXTRA_LAZY")
        
                 * @ORM\OrderBy({"createdAt" = "DESC"})
        
                 */
        
                private $comments;
        
                                                    ... lines 66 - 201
                            
            }

Now, go back to the page and refresh. We still have six queries, but go look at them. Awesome! Instead of selecting all of the comment data, they are super-fast COUNT queries!

Here's how this works: if you set fetch="EXTRA_LAZY", and you simply count the result of $article->getComments(), then instead of querying for all of the comments, Doctrine does a quick COUNT query.

Awesome, right! You might think that it's so awesome that this should always be the way it works! But, there is one situation where this is not ideal. And actually, we have it! Go to the article show page.

Here, we count the comments first, and then we loop over them:

            
            87 lines
            
            templates/article/show.html.twig
        
                                                    ... lines 1 - 4
                            
            {% block body %}
        
            <div class="container">
        
                <div class="row">
        
                    <div class="col-sm-12">
        
                        <div class="show-article-container p-3 mt-4">
        
                                                    ... lines 11 - 39
                            
                            <div class="row">
        
                                <div class="col-sm-12">
        
                                    <h3><i class="pr-3 fa fa-comment"></i>{{ article.comments|length }} Comments</h3>
        
                                                    ... lines 43 - 57
                            
                                    {% for comment in article.comments %}
        
                                                    ... lines 59 - 70
                            
                                    {% endfor %}
        
                                </div>
        
                            </div>
        
                        </div>
        
                    </div>
        
                </div>
        
            </div>
        
            {% endblock %}
        
                                                    ... lines 81 - 87

Look at the profiler now. Thanks to EXTRA_LAZY, we have an extra query! It counts the comments... but then, right after, it queries for all of them anyways. Before we were using EXTRA_LAZY, this count query didn't exist.

So, sorry people, like life, everything is a trade-off. But, it's still probably a net-win for us. But as always, don't prematurely optimize. Deploy first, identify performance issues, and then solve them.

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": "^7.1.3",
        "ext-iconv": "*",
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.7", // 1.7.0
        "knplabs/knp-paginator-bundle": "^2.7", // v2.7.2
        "knplabs/knp-time-bundle": "^1.8", // 1.8.0
        "nexylan/slack-bundle": "^2.0,<2.2.0", // v2.0.0
        "php-http/guzzle6-adapter": "^1.1", // v1.1.1
        "sensio/framework-extra-bundle": "^5.1", // v5.1.4
        "stof/doctrine-extensions-bundle": "^1.3", // v1.3.0
        "symfony/asset": "^4.0", // v4.0.4
        "symfony/console": "^4.0", // v4.0.14
        "symfony/flex": "^1.0", // v1.17.6
        "symfony/framework-bundle": "^4.0", // v4.0.14
        "symfony/lts": "^4@dev", // dev-master
        "symfony/orm-pack": "^1.0", // v1.0.6
        "symfony/twig-bundle": "^4.0", // v4.0.4
        "symfony/web-server-bundle": "^4.0", // v4.0.4
        "symfony/yaml": "^4.0", // v4.0.14
        "twig/extensions": "^1.5" // v1.5.1
    },
    "require-dev": {
        "doctrine/doctrine-fixtures-bundle": "^3.0", // 3.0.2
        "easycorp/easy-log-handler": "^1.0.2", // v1.0.4
        "fzaninotto/faker": "^1.7", // v1.7.1
        "symfony/debug-bundle": "^3.3|^4.0", // v4.0.4
        "symfony/dotenv": "^4.0", // v4.0.14
        "symfony/maker-bundle": "^1.0", // v1.4.0
        "symfony/monolog-bundle": "^3.0", // v3.1.2
        "symfony/phpunit-bridge": "^3.3|^4.0", // v4.0.4
        "symfony/profiler-pack": "^1.0", // v1.0.3
        "symfony/var-dumper": "^3.3|^4.0" // v4.0.4
    }
}

Edit on GitHub

TRACK

Symfony 4 >

COURSE

Mastering Doctrine Relations in Symfony 4!

OrderBy & fetch EXTRA_LAZY

Keep on Learning!

@ORM\OrderBy()

Fetch EXTRA_LAZY

Leave a comment!

What PHP libraries does this tutorial use?

	... lines 1 - 6
	<div class="container">
	<div class="row">
	<div class="col-sm-12">
	<div class="show-article-container p-3 mt-4">
	... lines 11 - 39
	<div class="row">
	<div class="col-sm-12">
	... lines 42 - 57
	{% for comment in article.comments %}
	<div class="row">
	<div class="col-sm-12">
	<img class="comment-img rounded-circle" src="{{ asset('images/alien-profile.png') }}">
	<div class="comment-container d-inline-block pl-3 align-top">
	<span class="commenter-name">{{ comment.authorName }}</span>
	<small>about {{ comment.createdAt\|ago }}</small>
	<br>
	<span class="comment"> {{ comment.content }}</span>
	<p><a href="#">Reply</a></p>
	</div>
	</div>
	</div>
	{% endfor %}

	</div>
	</div>
	</div>
	</div>
	</div>
	</div>
	... lines 79 - 87

	... lines 1 - 13
	class Article
	{
	... lines 16 - 170
	/**
	* @return Collection\|Comment[]
	*/
	public function getComments(): Collection
	{
	return $this->comments;
	}
	... lines 178 - 200
	}

	... lines 1 - 13
	class Article
	{
	... lines 16 - 60
	/**
	* @ORM\OneToMany(targetEntity="App\Entity\Comment", mappedBy="article")
	*/
	private $comments;
	... lines 65 - 200
	}

	... lines 1 - 2
	{% block body %}
	<div class="container">
	<div class="row">

	<!-- Article List -->

	<div class="col-sm-12 col-md-8">
	... lines 10 - 18
	<!-- Supporting Articles -->

	{% for article in articles %}
	<div class="article-container my-1">
	<a href="{{ path('article_show', {slug: article.slug}) }}">
	... line 24
	<div class="article-title d-inline-block pl-3 align-middle">
	... line 26
	<small>({{ article.comments\|length }} comments)</small>
	... lines 28 - 30
	</div>
	</a>
	</div>
	{% endfor %}
	</div>
	... lines 36 - 55
	</div>
	</div>
	{% endblock %}

	... lines 1 - 4
	{% block body %}

	<div class="container">
	<div class="row">
	<div class="col-sm-12">
	<div class="show-article-container p-3 mt-4">
	... lines 11 - 39
	<div class="row">
	<div class="col-sm-12">
	<h3><i class="pr-3 fa fa-comment"></i>{{ article.comments\|length }} Comments</h3>
	... lines 43 - 57
	{% for comment in article.comments %}
	... lines 59 - 70
	{% endfor %}

	</div>
	</div>
	</div>
	</div>
	</div>
	</div>

	{% endblock %}
	... lines 81 - 87