php - Add a property to a child entity in an association with Doctrine's QueryBuilder -

i'm working symfony 2.3 , doctrine 1.2

i have following entity structure, product, tag , tagcategory. product has manytomany relationship tag, , tag has manytoone relationship 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 have tag assigned      *      * @orm\manytomany(targetentity="product", mappedby="tags")      */     protected $products; } 

what need is, given list of products, tags these products have, and, each tag, number of products have tag.

i want retrieve tags grouped tagcategory because need rendered grouped.

here 1 of queries tried, it's on tagcategory repository:

$qb->select('c tagcategory, t tag, count(t) 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 array 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 tags in category, want, puts total in top level , total of last tag in category. need total property of tag entity.

i have tried doing query, in tag repository:

$qb->select('c tagcategory, t tag, count(t) 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 array of tags, category , total property, correct, need tags grouped inside category:

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

i need this, can done?

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 you.

what want do, cannot done single query on sql level.

you can 2 queries or 1 query subquery. still doctrine in background, don't think doctrine2.* can it. doubt doctrine1.* able to.

anyway, if don't need data tagcategory, best solution modify second query, , select additional field(s) need.

$qb->select('c tagcategory, t tag, count(t) total, c.name tagcategory_name')     ->from($this->_entityname, 't')     ->leftjoin('t.category', 'c')     ->leftjoin('t.products', 'p')     ->where(         $qb->expr()->in('p.id', $productsids)         )      ->groupby('t.id') ; 

if need, data, , exact structure described, select tagcategories 1 query , pair in foreach loop, id.