CODEWITHSUNDEEP
symfonydoctrine-orm
MetaDan
MetaDan

Reputation: 61

How to get count of related entity as a

Say I have 3 entities:

Tray, Cards, CardType

A Tray has Cards and each Card has a CardColour.

Using doctrine2 can I add a count of each to a query?

e.g.

$qb = $this->getDoctrine()->getManager()->getQueryBuilder();
$query = $qb->select('t',$qb->expr()->count('blueCards'))->from('Tray')
    ->leftJoin('t.Cards','blueCards')
    ->andWhere('bluecards.CardColour.col = :col')
    ->setParameter('col','blue');

and then also get counts of yellow cards and red cards as well.

For instance to end up with a load of:

Tray.name: tray1
Tray.blueCardsCount = 10
Tray.yellowCardsCount = 12
Tray.redCardsCount = 3

I hope that is explained well enough...

Upvotes: 1

Views: 834

Answers (2)

BentCoder
BentCoder

Reputation: 12740

I guess you can work from here! Query::HYDRATE_SCALAR returns an array. More

Tray

/**
 * @ORM\Entity(repositoryClass="Your\WhatBundle\Repository\TrayRepository")
 * @ORM\Table(name="tray")
 */
class Tray
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\OneToMany(
     *      targetEntity="Card",
     *      mappedBy="tray",
     *      cascade={"persist", "remove"}
     * )
     */
    protected $card;
}

Card

/**
 * @ORM\Entity
 * @ORM\Table(name="card")
 */
class Card
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\OneToMany(
     *      targetEntity="Type",
     *      mappedBy="card",
     *      cascade={"persist", "remove"}
     * )
     */
    protected $type;

    /**
     * @ORM\ManyToOne(
     *      targetEntity="Tray",
     *      inversedBy="card"
     * )
     * @ORM\JoinColumn(
     *      name="tray_id",
     *      referencedColumnName="id",
     *      onDelete="CASCADE",
     *      nullable=false
     * )
     */
    protected $tray;
}

Type

/**
 * @ORM\Entity
 * @ORM\Table(name="type")
 */
class Type
{
    /**
     * @ORM\Id
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(
     *      targetEntity="Card",
     *      inversedBy="type"
     * )
     * @ORM\JoinColumn(
     *      name="card_id",
     *      referencedColumnName="id",
     *      onDelete="CASCADE",
     *      nullable=false
     * )
     */
    protected $card;
}

Repository

class TrayRepository extends EntityRepository
{
    public function getCounts()
    {
        return
            $this
                ->createQueryBuilder('tr')
                ->select('COUNT(tr) AS TR, COUNT(tr) AS CR, COUNT(ty) AS TY')
                ->leftJoin('tr.card', 'cr')
                ->leftJoin('cr.type', 'ty')
                ->getQuery()
                ->getResult(Query::HYDRATE_SCALAR);
    }
}

Controller

class DefaultController extends Controller
{
    /**
     * @Route("")
     * @Method({"GET"})
     */
    public function indexAction()
    {
        $repo = $this->getDoctrine()->getRepository('YourWhatBundle:Tray');
        $result = $repo->getCounts();

        echo '<pre>';
        print_r($result);
    }
}

Result

Array
(
    [0] => Array
        (
            [TR] => 5
            [CR] => 5
            [TY] => 3
        )

)

Upvotes: 1

ejuhjav
ejuhjav

Reputation: 2710

you can combine the counts like this (update a bit to match your actual query code):

    $query = $this->getDoctrine()->getManager()->getRepository('[YourBundle]:Tray')->createQueryBuilder('tray')
        ->select('tray.name, count(blueCard) as blueCardsCount, count(yellowCard) as yellowCardsCount, count(redCard) as redCardsCount')
        ->leftJoin('tray.Cards', 'card')
        ->leftJoin('card.CardColour', 'blueCard', 'WITH', 'blueCard.col = "blue"')
        ->leftJoin('card.CardColour', 'redCard', 'WITH', 'redCard.col = "red"')
        ->leftJoin('card.CardColour', 'yellowCard', 'WITH', 'yellowCard.col = "yellow"')
        ->groupBy('tray.name') // or id...
        ->getQuery();

    $result = $query->getResult();

Just note that this returns the values in array (instead of entities).

Upvotes: 1

Related Questions