Doctrine Query builder, count related one to many rows

Question

<?php
namespace Raltech\WarehouseBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity
 * @ORM\Table(name="warehouse_magazine")
 */
class Magazine
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    protected $name;


    /**
     * @ORM\Column(type="text")
     */
    protected $description;


    /**
     * @ORM\OneToMany(targetEntity="Wardrobe", mappedBy="magazine",cascade={"remove"})
    */
    protected $wardrobe;


    public function __construct()
    {
        $this->wardrobe = new ArrayCollection();
    }

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return Magazine
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set description
     *
     * @param string $description
     * @return Magazine
     */
    public function setDescription($description)
    {
        $this->description = $description;

        return $this;
    }

    /**
     * Get description
     *
     * @return string 
     */
    public function getDescription()
    {
        return $this->description;
    }

    /**
     * Add wardrobe
     *
     * @param \Raltech\WarehouseBundle\Entity\Wardrobe $wardrobe
     * @return Magazine
     */
    public function addWardrobe(\Raltech\WarehouseBundle\Entity\Wardrobe $wardrobe)
    {
        $this->wardrobe[] = $wardrobe;

        return $this;
    }

    /**
     * Remove wardrobe
     *
     * @param \Raltech\WarehouseBundle\Entity\Wardrobe $wardrobe
     */
    public function removeWardrobe(\Raltech\WarehouseBundle\Entity\Wardrobe $wardrobe)
    {
        $this->wardrobe->removeElement($wardrobe);
    }

    /**
     * Get wardrobe
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getWardrobe()
    {
        return $this->wardrobe;
    }
}


<?php
namespace Raltech\WarehouseBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="warehouse_wardrobe")
 */
class Wardrobe
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    protected $name;


    /**
     * @ORM\Column(type="text")
     */
    protected $description;


    /**
     * @ORM\ManyToOne(targetEntity="Magazine", inversedBy="wardrobe")
     * @ORM\JoinColumn(name="magazine_id", referencedColumnName="id")
     */
    protected $magazine;

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return Wardrobe
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set description
     *
     * @param string $description
     * @return Wardrobe
     */
    public function setDescription($description)
    {
        $this->description = $description;

        return $this;
    }

    /**
     * Get description
     *
     * @return string 
     */
    public function getDescription()
    {
        return $this->description;
    }

    /**
     * Set magazine
     *
     * @param \Raltech\WarehouseBundle\Entity\Magazine $magazine
     * @return Wardrobe
     */
    public function setMagazine(\Raltech\WarehouseBundle\Entity\Magazine $magazine = null)
    {
        $this->magazine = $magazine;

        return $this;
    }

    /**
     * Get magazine
     *
     * @return \Raltech\WarehouseBundle\Entity\Magazine 
     */
    public function getMagazine()
    {
        return $this->magazine;
    }
}

My 2 entites, i want count how many Wardrobes is related for each magazines, i must make this from querybuilder

$em = $this->get('doctrine.orm.entity_manager');
$userRepository = $em->getRepository('Raltech\WarehouseBundle\Entity\Magazine');
$qb = $userRepository->createQueryBuilder('magazine')
    ->addSelect("magazine.id,magazine.name,magazine.description")
    ->InnerJoin('magazine.wardrobe', 'wardrobe')
    ->addSelect('COUNT(wardrobe.id) AS wardrobecount')

This didn't work of course. So, somebody can me provide example how to count this from querybuilder?

Answer 1

Summarising the comments:

$qb = $userRepository->createQueryBuilder('magazine')
->addSelect("magazine.id,magazine.name,magazine.description")
->leftJoin('magazine.wardrobe', 'wardrobe') // To show as well the magazines without wardrobes related
->addSelect('COUNT(wardrobe.id) AS wardrobecount')
->groupBy('magazine.id'); // To group the results per magazine