1

Consider the following case: I have two entities: Article and ArticleComment:

// \AppBundle\Entity\Article

/**
 * @ORM\OneToMany(targetEntity="ArticleComment", mappedBy="article")
 */
private $comments;

I need to store the amount of comments in a field on the article (eg. articles.comments_count). The field needs to be updated whenever a comment is created or deleted.

Previously I used the CakePHP framework which has built-in CounterCache behavior which does this automatically. I've tried my best to find something similar for Doctrine 2 (starting with DoctrineExtensions library) but nothing seems to do what I'm looking for.

Any library that does this? Or do I have to come up with my own solution?

Edit: I've tried using Entity Events but I require this behavior on many entities so I'm interested in a reusable solution

Xymanek
  • 1,357
  • 14
  • 25

2 Answers2

1

You can take a look at the extra lazy associations. http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/extra-lazy-associations.html

This way you don't need to store the comment_counter as you will be able to use the count() function on your collection without loading the full collection. Internally, Doctrine will issue a "select count" query.

Keen
  • 108
  • 5
  • Unfortunately that won't work for me since I need to sort by the counter and also display it for 15 articles per page (I'm using KnpPaginator)... P.S. Sorry for late reply – Xymanek Sep 18 '16 at 18:33
  • So your question is not related to Doctrine. – Keen Sep 19 '16 at 07:08
  • It is. KnpPaginator is used to show a list of articles (by page, `?page=2`) but for every article I need to show the title, author and the amount of comments. Using the method you suggested would generate **15** additional queries per page load (which isn't good). This also doesn't give me a way to order based on number of comments (eg. `ORDER BY comments_count DESC`) – Xymanek Sep 19 '16 at 19:37
  • With the OneToMany relation only, you've all information needed to get what you want. You don't need to store an aggregate like comment_counter which can be computed with a simple count/groupby query. You need to build your query and then to pass it to the paginator. – Keen Sep 19 '16 at 22:19
0

Here is another answer which avoids storing this kind of aggregate and enables you to use the paginator as you've requested in comments. I didn't test it yet so there could be some errors.

$qb = $em->createQueryBuilder();
$qb
  ->select('a.title, a.author, count(c)')
  ->from('Article', 'a')
  ->leftJoin('a.comments', 'c')
  ->groupBy('a.id');

$paginator = $this->get('knp_paginator'); 
$pagination = $paginator->paginate($qb, $page, $limit);

As I said, this issue is not really Doctrine related because your initial model design is bad. Usually, you don't need to store an aggregate which can be computed with a count/groupby query. This kind of aggregate is useful when you have a lot of joined entities which creates a real overhead during computing. Else, you don't need it.

Keen
  • 108
  • 5