Symfony3 querybuilder命令数不胜数

Question

I have an entity called Tag, it has 3 ManyToMany relations fests, articles and news.

AppBundle\Entity\Tag:
    type: entity
    table: tag
    ...
  manyToMany:
      news:
          orderBy: { 'posted': 'DESC' }
          targetEntity: News
          inversedBy: tags
          joinTable:
            name: news_tag
            joinColumns:
              tag_id:
                referencedColumnName: id
            inverseJoinColumns:
              news_id:
                referencedColumnName: id
      articles:
          orderBy: { 'posted': 'DESC' }
          targetEntity: Article
          inversedBy: tags
          joinTable:
            name: article_tag
            joinColumns:
              tag_id:
                referencedColumnName: id
            inverseJoinColumns:
              article_id:
                referencedColumnName: id
      fests:
          orderBy: { 'when_starts': 'DESC', 'when_ends': 'DESC' }
          targetEntity: Fest
          inversedBy: tags
          joinTable:
            name: fest_tag
            joinColumns:
              tag_id:
                referencedColumnName: id
            inverseJoinColumns:
              fest_id:
                referencedColumnName: id

Now I want to write a createQueryBuilder that orders by the count "news" + count "articles" + count "fests", just for finding "TOP TAGS". I found solution only for one manyToMany relation.

Is there any way to do this?

EDIT: What I have:

    $qb->select(array(
        't.id', 
        't.name', 
        'COUNT(f) as festcount',
        'COUNT(n) as newscount',
        'COUNT(a) as articlescount',
        '(COUNT(f) + COUNT(n) + COUNT(a)) as totalcount'
        ))
    ->from('AppBundle:Tag', 't')
    ->leftJoin('t.fests', 'f')
    ->leftJoin('t.articles', 'a')
    ->leftJoin('t.news', 'n')
    ->groupBy('t.id')
    ->orderBy('totalcount', 'DESC');

But it giving fake results. When some tag has 3 fests, 1 article and 0 news, result is festcount = 3, newscount = 0, articlescount = 3 and totalcount = 6, and it should be 4.

Answer 1

Try this code:

$qb->select(array(
        't.id', 
        't.name', 
        'COUNT(DISTINCT f.id) as festcount',
        'COUNT(DISTINCT n.id) as newscount',
        'COUNT(DISTINCT a.id) as articlescount',
        '(festcount + newscount + articlescount) as totalcount'
        ))
    ->from('AppBundle:Tag', 't')
    ->leftJoin('t.fests', 'f')
    ->leftJoin('t.articles', 'a')
    ->leftJoin('t.news', 'n')
    ->groupBy('t.id')
    ->orderBy('totalcount', 'DESC');

If you'll need further help, please provide dump of those tables.