Add child object property in association with Doctrine QueryBuilder

I am working with Symfony 2.3 and doctrine 1.2

I have the following entity structure: Product, Tag and TagCategory. Product has a ManyToMany relationship with tag and tag has a ManyToOne relationship with TagCategory

Product class:

class Product
{
    /**
     * @var ArrayCollection List of tags
     *
     * @ORM\ManyToMany(targetEntity="Tag", inversedBy="products")
     * @ORM\JoinTable(name="tags_productos",
     *      joinColumns={@ORM\JoinColumn(name="cod_pro", referencedColumnName="cod_pro")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="cod_tag", referencedColumnName="id")}
     *      )
     */
    protected $tags;
}

      

        
        
        
      

    

TagCategory class:

class TagCategory extends BaseTagCategory
{
    /**
     * @ORM\OneToMany(targetEntity="Tag", mappedBy="category", orphanRemoval=true)
     */
    protected $tags;
}

      

        
        
        
      

    

Tag class:

class Tag extends BaseTag
{
    /**
     * @Assert\NotBlank()
     * @ORM\ManyToOne(targetEntity="TagCategory", inversedBy="tags")
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id", nullable=false)
     */
    protected $category;

    /**
     * @var ArrayCollection List of products that have this tag assigned
     *
     * @ORM\ManyToMany(targetEntity="Product", mappedBy="tags")
     */
    protected $products;
}

      

        
        
        
      

    

I need to do so that for a given list of products, get the tags that these products have, and for each tag, get the number of products that have this tag.

I also want to get the tags grouped by TagCategory because they need to be displayed grouped.

Here is one of the requests I tried on the TagCategory repository :

$qb->select('c as tagCategory, t as tag, COUNT(t) as total')
        ->from($this->_entityName, 'c')
        ->leftJoin('c.tags', 't')
        ->leftJoin('t.products', 'p')
        ->where(
            $qb->expr()->in('p.id', $productsIds)
            )
        ->groupBy('t.id')
    ;

      

        
        
        
      

    

This gives me an array with the following structure:

array(3) {
    [0]=> array(2) {
        ["tagCategory"]=> array(4) {
            ["id"]=>
            ["name"]=>
            ["slug"]=>
            ["tags"]=> array(2) {
                [0]=> array(4) {
                    ["id"]=>
                    ["order"]=>
                    ["name"]=>
                    ["slug"]=>
                }
                [1]=> array(4) {
                    ["id"]=>
                    ["order"]=>
                    ["name"]=>
                    ["slug"]=>
                }
            }
        }
        ["total"]=>
}

      

        
        
        
      

    

This query groups all the tags in a category that I want, but it sums the total at the top level and the total of the last tag in the category. I need the sum as another property of the Tag object .

I also tried this request on the Tag repository :

$qb->select('c as tagCategory, t as tag, COUNT(t) as total')
        ->from($this->_entityName, 't')
        ->leftJoin('t.category', 'c')
        ->leftJoin('t.products', 'p')
        ->where(
            $qb->expr()->in('p.id', $productsIds)
            )
        ->groupBy('t.id')
    ;

      

        
        
        
      

    

This query gives me an array of tags with category and amount as a property, which is correct, but I need the tags to be grouped within a category:

array(4) {
    [0]=> array(2) {
        ["tag"]=> array(5) {
            ["id"]=>
            ["order"]=>
            ["name"]=>
            ["slug"]=>
            ["category"]=> array(3) {
                ["id"]=>
                ["name"]=>
                ["slug"]=>
            }
        }
        ["total"]=>
}

      

        
        
        
      

    

I need something like this, is it possible to do it?

array(3) {
    [0]=> array(2) {
        ["tagCategory"]=> array(4) {
            ["id"]=>
            ["name"]=>
            ["slug"]=>
            ["tags"]=> array(2) {
                [0]=> array(4) {
                    ["id"]=>
                    ["order"]=>
                    ["name"]=>
                    ["slug"]=>
                    **["total"]=>**
                }
                [1]=> array(4) {
                    ["id"]=>
                    ["order"]=>
                    ["name"]=>
                    ["slug"]=>
                    **["total"]=>**
                }
            }
        }
}

      

        
        
        
      

    

Thank.