Buy
Buy

Fetching Relations

Yes! Each Article is now related to two comments in the database. So, on the article show page, it's time to get rid of this hardcoded stuff and, finally, query for the true comments for this Article.

In src/Controller, open ArticleController and find the show() action:

... lines 1 - 16
class ArticleController extends AbstractController
{
... lines 19 - 40
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
if ($article->getSlug() === 'khaaaaaan') {
$slack->sendMessage('Kahn', 'Ah, Kirk, my old friend...');
}
$comments = [
'I ate a normal rock once. It did NOT taste like bacon!',
'Woohoo! I\'m going on an all-asteroid diet!',
'I like bacon too! Buy some from my site! bakinsomebacon.com',
];
return $this->render('article/show.html.twig', [
'article' => $article,
'comments' => $comments,
]);
}
... lines 61 - 73
}

This renders a single article. So, how can we find all of the comments related to this article? Well, we already know one way to do this.

Remember: whenever you need to run a query, step one is to get that entity's repository. And, surprise! When we generated the Comment class, the make:entity command also gave us a new CommentRepository:

... lines 1 - 2
namespace App\Repository;
use App\Entity\Comment;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Symfony\Bridge\Doctrine\RegistryInterface;
/**
* @method Comment|null find($id, $lockMode = null, $lockVersion = null)
* @method Comment|null findOneBy(array $criteria, array $orderBy = null)
* @method Comment[] findAll()
* @method Comment[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class CommentRepository extends ServiceEntityRepository
{
public function __construct(RegistryInterface $registry)
{
parent::__construct($registry, Comment::class);
}
... lines 21 - 49
}

Thanks MakerBundle!

Get the repository by adding a CommentRepository argument. Then, let's see, could we use one of the built-in methods? Try $comments = $commentRepository->findBy(), and pass this article set to the entire $article object:

... lines 1 - 6
use App\Repository\CommentRepository;
... lines 8 - 17
class ArticleController extends AbstractController
{
... lines 20 - 41
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack, CommentRepository $commentRepository)
{
if ($article->getSlug() === 'khaaaaaan') {
... line 48
}
$comments = $commentRepository->findBy(['article' => $article]);
... lines 52 - 63
}
... lines 65 - 77
}

Dump these comments and die:

... lines 1 - 17
class ArticleController extends AbstractController
{
... lines 20 - 41
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack, CommentRepository $commentRepository)
{
... lines 47 - 50
$comments = $commentRepository->findBy(['article' => $article]);
dump($comments);die;
... lines 53 - 63
}
... lines 65 - 77
}

Then, find your browser and, try it! Yes! It returns the two Comment objects related to this Article!

So, the weird thing is that, once again, you need to stop thinking about the columns in your tables, like article_id, and only think about the properties on your entity classes. That's why we use 'article' => $article. Of course, behind the scenes, Doctrine will make a query where article_id = the id from this Article. But, in PHP, we think all about objects.

Fetching Comments Directly from Article

As nice as this was... there is a much simpler way! When we generated the relationship, it asked us if we wanted to add an optional comments property to the Article class, for convenience. We said yes! And thanks to that, we can literally say $comments = $article->getComments(). Dump $comments again:

... lines 1 - 13
class ArticleController extends AbstractController
{
... lines 16 - 37
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
... lines 43 - 46
$comments = $article->getComments();
dump($comments);die;
... lines 49 - 59
}
... lines 61 - 73
}

Oh, and now, we don't need the CommentRepository anymore:

... lines 1 - 13
class ArticleController extends AbstractController
{
... lines 16 - 37
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
... lines 43 - 59
}
... lines 61 - 73
}

Cool.

Lazy Loading

Head back to your browser and, refresh! It's the exact same as before. Wait, what? What's this weird PersistentCollection thing?

Here's what's going on. When Symfony queries for the Article, it only fetches the Article data: it does not automatically fetch the related Comments. And, for performance, that's great! We may not even need the comment data! But, as soon as we call getComments() and start using that, Doctrine makes a query in the background to go get the comment data.

This is called "lazy loading": related data is not queried for until, and unless, we use it. To make this magic possible, Doctrine uses this PersistentCollection object. This is not something you need to think or worry about: this object looks and acts like an array.

To prove it, let's foreach over $comments as $comment and dump each $comment inside. Put a die at the end:

... lines 1 - 13
class ArticleController extends AbstractController
{
... lines 16 - 37
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
... lines 43 - 46
$comments = $article->getComments();
foreach ($comments as $comment) {
dump($comment);
}
die;
... lines 52 - 62
}
... lines 64 - 76
}

Try it again! Boom! Two Comment objects!

Fetching the Comments in the Template

Back in the controller, we no longer need these hard-coded comments. In fact, we don't even need to pass comments into the template at all! That's because we can call the getComments() method directly from Twig!

Remove all of the comment logic:

... lines 1 - 13
class ArticleController extends AbstractController
{
... lines 16 - 37
/**
* @Route("/news/{slug}", name="article_show")
*/
public function show(Article $article, SlackClient $slack)
{
if ($article->getSlug() === 'khaaaaaan') {
$slack->sendMessage('Kahn', 'Ah, Kirk, my old friend...');
}
return $this->render('article/show.html.twig', [
'article' => $article,
]);
}
... lines 51 - 63
}

And then, jump into templates/article/show.html.twig. Scroll down a little... ah, yes! First, update the count: article.comments|length:

... 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 - 71
</div>
</div>
</div>
</div>
</div>
</div>
{% endblock %}
... lines 80 - 86

Easy! Then, below, change the loop to use for comment in article.comments:

... 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 - 69
{% endfor %}
</div>
</div>
</div>
</div>
</div>
</div>
{% endblock %}
... lines 80 - 86

And because each comment has a dynamic author, print that with {{ comment.authorName }}. And the content is now comment.content:

... 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 %}
<div class="row">
<div class="col-sm-12">
... line 61
<div class="comment-container d-inline-block pl-3 align-top">
<span class="commenter-name">{{ comment.authorName }}</span>
... lines 64 - 65
<span class="comment"> {{ comment.content }}</span>
... line 67
</div>
</div>
</div>
{% endfor %}
</div>
</div>
</div>
</div>
</div>
</div>
{% endblock %}
... lines 81 - 87

Oh, and, because each comment has a createdAt, let's print that too, with our trusty ago filter:

... 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 %}
<div class="row">
<div class="col-sm-12">
... line 61
<div class="comment-container d-inline-block pl-3 align-top">
<span class="commenter-name">{{ comment.authorName }}</span>
<small>about {{ comment.createdAt|ago }}</small>
... line 65
<span class="comment"> {{ comment.content }}</span>
... line 67
</div>
</div>
</div>
{% endfor %}
</div>
</div>
</div>
</div>
</div>
</div>
{% endblock %}
... lines 81 - 87

Love it! Let's try it! Go back, refresh and... yes! Two comments, from about 17 minutes ago. And, check this out: on the web debug toolbar, you can see that there are two database queries. The first query selects the article data only. And the second selects all of the comment data where article_id matches this article's id - 112. This second query doesn't actually happen until we reference the comments from inside of 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

That laziness is a key feature of Doctrine relations.

Next, it's time to talk about the subtle, but super-important distinction between the owning and inverse sides of a relation.

Leave a comment!

  • 2019-04-04 Diego Aguiar

    Ohh, that's because of your MySql version, please upgrade to 5.7+ or if that's not possible you will have to limit the length of your fields to 181 characters I believe.

  • 2019-04-04 hanene

    Hi Diego
    when I run this command php bin/console:schema:create I got this error:
    Schema-Tool failed with Error 'An exception occurred while executing 'CREATE TABLE migration_versions (version VARCHAR(255) NOT NULL, PRIMARY KEY
    (version)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':
    SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes' while executing DDL: CREATE TABL
    E migration_versions (version VARCHAR(255) NOT NULL, PRIMARY KEY(version)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = Inno
    DB
    SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes..
    And thank you for your assistance :)))

  • 2019-04-03 Diego Aguiar

    Hey hanene, sorry for the late reply

    Your queries looks fine to me. The first one is trying to get all comments for given Article ID (64), and the second one is trying to find an Article from its slug field

    If you think you have a problem with your DB, you can recreate it from scratch like this:


    bin/console doctrine:database:drop --force
    bin/console doctrine:database:create
    bin/console doctrine:schema:create
    bin/console doctrine:fixtures:load

    Cheers!

  • 2019-03-30 hanene

    Hi Diego
    the doctrine connection had the access to database and in symfony profiler there are two queries that were executed

    #▼ Time Info
    2 1.00 ms
    SELECT t0.id AS id_1, t0.author_name AS author_name_2, t0.content AS content_3, t0.created_at AS created_at_4, t0.updated_at AS updated_at_5, t0.article_id AS article_id_6 FROM comment t0 WHERE t0.article_id = ?
    Parameters:
    [▼
    64
    ]
    Hide formatted query Hide runnable query Explain query
    SELECT
    t0.id AS id_1,
    t0.author_name AS author_name_2,
    t0.content AS content_3,
    t0.created_at AS created_at_4,
    t0.updated_at AS updated_at_5,
    t0.article_id AS article_id_6
    FROM
    comment t0
    WHERE
    t0.article_id = ?
    SELECT t0.id AS id_1, t0.author_name AS author_name_2, t0.content AS content_3, t0.created_at AS created_at_4, t0.updated_at AS updated_at_5, t0.article_id AS article_id_6 FROM comment t0 WHERE t0.article_id = 64;
    1 4.00 ms
    SELECT t0.id AS id_1, t0.title AS title_2, t0.slug AS slug_3, t0.content AS content_4, t0.published_at AS published_at_5, t0.price AS price_6, t0.name AS name_7, t0.author AS author_8, t0.heart_count AS heart_count_9, t0.image_filename AS image_filename_10, t0.created_at AS created_at_11, t0.updated_at AS updated_at_12 FROM article t0 WHERE t0.slug = ? LIMIT 1
    Parameters:

  • 2019-03-29 Diego Aguiar

    Ohh, the findBy() method expects a criteria, in other words, your WHERE clause, so it can filter the records. Probably you where passing something that matches nothing in your table? That's why I recommend you to inspect the executed queries via the profiler :)

  • 2019-03-29 hanene

    Thank you for your response
    I think findBy method is not working but findAll returns records of comments

  • 2019-03-27 Diego Aguiar

    And you are using the CommentRepository in your controller, right? Can you double check that you are on the dev environment? or that you loaded the fixtures for the dev environment
    Another thing you can check is the profiler at the "Doctrine" tab, so you can see which queries are being executed

  • 2019-03-26 hanene

    Hi Diego
    I already loaded my comments and when I use php bin/console doctrine:query:sql 'SELECT * FROM comment' it show up normally but in my controller when I fetching for comments it doesn't work

    ArticleController.php on line 80:
    []
    I think my problem is that I migrate comment table it produced following error ::
    SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'comment' already exists

  • 2019-03-25 Diego Aguiar

    Hey hanene

    If you already load the fixtures (or manually added some comments), you should see the comments. So, first double check the database records and if you see comments there, then maybe you are not fetching from the right repository. Double check you are injecting the CommenRepository.

    Cheers!

  • 2019-03-25 hanene

    hi everyone
    I get empty comments, I don't understand why when Im using the find() methods in Entity Repository I got empty array
    Thank you for your help..

  • 2018-12-13 Usuri

    I watch that video about join and it works. Thank you.

  • 2018-12-11 weaverryan

    Hey Usuri!

    You have two options for this:

    1) You can add a fetch="EAGER" option to your OneToMany annotation. But that will fetch them "eager" in ALL cases.

    2 Create a custom query that JOINs to the comments and fetches them. We do that here: https://symfonycasts.com/sc.... Well, that is an example of starting with a Comment and joining back to an Article. You would do the opposite: start with an Article, then leftJoin over to comments.

    Let me know if this helps!

    Cheers!

  • 2018-12-10 Usuri

    Hello.
    How can I fetch comments using "eager loading". Lazy loading it's not always best option.

  • 2018-09-10 Diego Aguiar

    Hey Mylan

    I believe your DB schema is out of sync, I mean, you added a new property to your entity but didn't update the schema

    php ./bin/console doctrine:schema:update --force

    pass --dump-sql first so you can see the SQL which will be executed

    Cheers!

  • 2018-09-10 Mylan

    Hello I get a error with:
    An exception has been thrown during the rendering of a template ("An exception occurred while executing 'SELECT t0.id AS id_1, t0.author_name AS author_name_2, t0.content AS content_3, t0.created_at AS created_at_4, t0.updated_at AS updated_at_5, t0.article_id AS article_id_6 FROM comment t0 WHERE t0.article_id = ?' with params [49]:

    SQLSTATE[42S22]: Column not found: 1054 Unknown column 't0.created_at' in 'field list'"). Please help me out

  • 2018-09-04 Victor Bocharsky

    Hey Dmitriy,

    Yeah, you nailed it! That's exactly the best way to check it. The "empty($comments)" always returns false because you check that $comments variable is empty, but that's not true, it's always an object. And thanks for sharing your solution with others!

    Cheers!

  • 2018-09-04 Дмитрий Ченгаев

    I find it's

    $article->getComments()->isEmpty()

  • 2018-09-04 Дмитрий Ченгаев

    How can I check that the PersistentCollection is empty?

    $comments = $article->getComments();

    empty($comments)

    Always return false.