Doctrine QueryBuilder COUNT and Voters

Question

I have some entities, for example Device entity with a voter allowing the current user to access or not some Devices.

When searching for Devices, in order to filter, I user an array_filter function which is working well.

But, I want to make some stats on my Device entity, for example the number of Devices by Brand.

My Query is OK :

$query = $this->createQueryBuilder('d')
            ->select('COUNT(d.id), b.name')
            ->join('d.model', 'm')
            ->join('m.Brand', 'b')
            ->groupBy('b.name')
         ;

        return $query->getQuery()->getResult();

I have an array with my datas.

But the voter doesn't apply.

If I switch the user to one who may not have access to all Devices, I still see the same numbers.

So, how can I filter my COUNT request with the voters ?

The Device Entity :

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;


/**
 * @ORM\Entity(repositoryClass="App\Repository\DeviceRepository")
 */
class Device
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=15)
     */
    private $reference;

    /**
     * @ORM\Column(type="string", length=20)
     */
    private $imei;

    /**
     * @ORM\Column(type="date", nullable=true)
     */
    private $buyDate;

    /**
     * @ORM\Column(type="float", nullable=true)
     */
    private $buyPrice;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\DeviceGrade")
     */
    private $grade;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\CustomerGroup")
     * @ORM\JoinColumn(nullable=false)
     */
    private $customerGroup;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\CustomerEntity")
     */
    private $customerEntity;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\CustomerSite")
     */
    private $customerSite;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Model")
     * @ORM\JoinColumn(nullable=false)
     */
    private $model;

    /**
     * @ORM\Column(type="date", nullable=true)
     */
    private $sellDate;

    /**
     * @ORM\Column(type="float", nullable=true)
     */
    private $sellPrice;


    /**
     * @ORM\Column(type="datetime", nullable=true)
     * @Gedmo\Timestampable(on="create")
     */
    private $dateAdd;

    /**
     * @ORM\Column(type="datetime", nullable=true)
     * @Gedmo\Timestampable(on="update")
     */
    private $dateUpd;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User")
     * @Gedmo\Blameable(on="create")
     */
    private $createdBy;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User")
     * @Gedmo\Blameable(on="update")
     */
    private $modifiedBy;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\DeviceStatusHistory", mappedBy="device")
     */
    private $deviceStatusHistories;


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

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getReference(): ?string
    {
        return $this->reference;
    }

    public function setReference(string $reference): self
    {
        $this->reference = $reference;

        return $this;
    }

    public function getImei(): ?string
    {
        return $this->imei;
    }

    public function setImei(string $imei): self
    {
        $this->imei = $imei;

        return $this;
    }

    public function getBuyDate(): ?\DateTimeInterface
    {
        return $this->buyDate;
    }

    public function setBuyDate(?\DateTimeInterface $buyDate): self
    {
        $this->buyDate = $buyDate;

        return $this;
    }

    public function getBuyPrice(): ?float
    {
        return $this->buyPrice;
    }

    public function setBuyPrice(?float $buyPrice): self
    {
        $this->buyPrice = $buyPrice;

        return $this;
    }

    public function getGrade(): ?DeviceGrade
    {
        return $this->grade;
    }

    public function setGrade(?DeviceGrade $grade): self
    {
        $this->grade = $grade;

        return $this;
    }

    public function getCustomerGroup(): ?CustomerGroup
    {
        return $this->customerGroup;
    }

    public function setCustomerGroup(?CustomerGroup $customerGroup): self
    {
        $this->customerGroup = $customerGroup;

        return $this;
    }

    public function getCustomerEntity(): ?CustomerEntity
    {
        return $this->customerEntity;
    }

    public function setCustomerEntity(?CustomerEntity $customerEntity): self
    {
        $this->customerEntity = $customerEntity;

        return $this;
    }

    public function getCustomerSite(): ?CustomerSite
    {
        return $this->customerSite;
    }

    public function setCustomerSite(?CustomerSite $customerSite): self
    {
        $this->customerSite = $customerSite;

        return $this;
    }

    public function getModel(): ?Model
    {
        return $this->model;
    }

    public function setModel(?Model $model): self
    {
        $this->model = $model;

        return $this;
    }

    public function getSellDate(): ?\DateTimeInterface
    {
        return $this->sellDate;
    }

    public function setSellDate(?\DateTimeInterface $sellDate): self
    {
        $this->sellDate = $sellDate;

        return $this;
    }

    public function getSellPrice(): ?float
    {
        return $this->sellPrice;
    }

    public function setSellPrice(?float $sellPrice): self
    {
        $this->sellPrice = $sellPrice;

        return $this;
    }

    public function getDateAdd(): ?\DateTimeInterface
    {
        return $this->dateAdd;
    }

    public function setDateAdd(\DateTimeInterface $dateAdd): self
    {
        $this->dateAdd = $dateAdd;

        return $this;
    }

    public function getDateUpd(): ?\DateTimeInterface
    {
        return $this->dateUpd;
    }

    public function setDateUpd(\DateTimeInterface $dateUpd): self
    {
        $this->dateUpd = $dateUpd;

        return $this;
    }

    public function getCreatedBy(): ?User
    {
        return $this->createdBy;
    }

    public function setCreatedBy(?User $createdBy): self
    {
        $this->createdBy = $createdBy;

        return $this;
    }

    public function getModifiedBy(): ?User
    {
        return $this->modifiedBy;
    }

    public function setModifiedBy(?User $modifiedBy): self
    {
        $this->modifiedBy = $modifiedBy;

        return $this;
    }

    /**
     * @return Collection|DeviceStatusHistory[]
     */
    public function getDeviceStatusHistories(): Collection
    {
        return $this->deviceStatusHistories;
    }

    public function addDeviceStatusHistory(DeviceStatusHistory $deviceStatusHistory): self
    {
        if (!$this->deviceStatusHistories->contains($deviceStatusHistory)) {
            $this->deviceStatusHistories[] = $deviceStatusHistory;
            $deviceStatusHistory->setDevice($this);
        }

        return $this;
    }

    public function removeDeviceStatusHistory(DeviceStatusHistory $deviceStatusHistory): self
    {
        if ($this->deviceStatusHistories->contains($deviceStatusHistory)) {
            $this->deviceStatusHistories->removeElement($deviceStatusHistory);
            // set the owning side to null (unless already changed)
            if ($deviceStatusHistory->getDevice() === $this) {
                $deviceStatusHistory->setDevice(null);
            }
        }

        return $this;
    }

    public function __toString(): string
    {
        return $this->reference.' / '.$this->imei.' / '.$this->getModel()->getName().' - '.
            $this->getModel()->getBrand()->getName().' - '.$this->getModel()->getColor()->getName().
            ' - '.$this->getModel()->getStorage()->getCapacity();
    }


}

The Model Entity :

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;
use Vich\UploaderBundle\Mapping\Annotation as Vich;
use Symfony\Component\HttpFoundation\File\File;




/**
 * @ORM\Entity(repositoryClass="App\Repository\ModelRepository")
 * @Vich\Uploadable
 */
class Model
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

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

    /**
     * @ORM\Column(type="boolean")
     */
    private $isActive;

    /**
     * @ORM\Column(type="date", nullable=true)
     */
    private $dateStartSell;

    /**
     * @ORM\Column(type="date", nullable=true)
     */
    private $dateEndSell;

    /**
     * @ORM\Column(type="date", nullable=true)
     */
    private $dateEndSupport;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Brand", inversedBy="models")
     * @ORM\JoinColumn(nullable=false)
     */
    private $Brand;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Color")
     * @ORM\JoinColumn(nullable=false)
     */
    private $color;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Storage")
     */
    private $storage;

    /**
     * @ORM\Column(type="datetime", nullable=true)
     * @Gedmo\Timestampable(on="create")
     */
    private $dateAdd;

    /**
     * @ORM\Column(type="datetime", nullable=true)
     * @Gedmo\Timestampable(on="update")
     */
    private $dateUpd;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User")
     * @Gedmo\Blameable(on="create")
     */
    private $createdBy;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User")
     * @Gedmo\Blameable(on="update")
     */
    private $modifiedBy;

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\CustomerGroup", inversedBy="models")
     */
    private $customerGroup;

    /**
     * NOTE: This is not a mapped field of entity metadata, just a simple property.
     *
     * @Vich\UploadableField(mapping="model_image", fileNameProperty="imageName", size="imageSize")
     *
     * @var File
     */
    private $imageFile;

    /**
     * @ORM\Column(type="string", length=255, nullable=true)
     *
     * @var string
     */
    private $imageName;

    /**
     * @ORM\Column(type="integer", nullable=true)
     *
     * @var integer
     */
    private $imageSize;



    public function __construct(?File $imageFile = null)
    {
        $this->customerGroup = new ArrayCollection();
        $this->imageFile = $imageFile;

        if (null !== $imageFile) {
            // It is required that at least one field changes if you are using doctrine
            // otherwise the event listeners won't be called and the file is lost
            $this->dateUpd = new \DateTimeImmutable();
        }
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }

    public function getIsActive(): ?bool
    {
        return $this->isActive;
    }

    public function setIsActive(bool $isActive): self
    {
        $this->isActive = $isActive;

        return $this;
    }

    public function getDateStartSell(): ?\DateTimeInterface
    {
        return $this->dateStartSell;
    }

    public function setDateStartSell(?\DateTimeInterface $dateStartSell): self
    {
        $this->dateStartSell = $dateStartSell;

        return $this;
    }

    public function getDateEndSell(): ?\DateTimeInterface
    {
        return $this->dateEndSell;
    }

    public function setDateEndSell(?\DateTimeInterface $dateEndSell): self
    {
        $this->dateEndSell = $dateEndSell;

        return $this;
    }

    public function getDateEndSupport(): ?\DateTimeInterface
    {
        return $this->dateEndSupport;
    }

    public function setDateEndSupport(?\DateTimeInterface $dateEndSupport): self
    {
        $this->dateEndSupport = $dateEndSupport;

        return $this;
    }

    public function getBrand(): ?Brand
    {
        return $this->Brand;
    }

    public function setBrand(?Brand $Brand): self
    {
        $this->Brand = $Brand;

        return $this;
    }

    public function getColor(): ?Color
    {
        return $this->color;
    }

    public function setColor(?Color $color): self
    {
        $this->color = $color;

        return $this;
    }

    public function getStorage(): ?Storage
    {
        return $this->storage;
    }

    public function setStorage(?Storage $storage): self
    {
        $this->storage = $storage;

        return $this;
    }

    public function getDateAdd(): ?\DateTimeInterface
    {
        return $this->dateAdd;
    }

    public function setDateAdd(\DateTimeInterface $dateAdd): self
    {
        $this->dateAdd = $dateAdd;

        return $this;
    }

    public function getDateUpd(): ?\DateTimeInterface
    {
        return $this->dateUpd;
    }

    public function setDateUpd(\DateTimeInterface $dateUpd): self
    {
        $this->dateUpd = $dateUpd;

        return $this;
    }

    public function getCreatedBy(): ?User
    {
        return $this->createdBy;
    }

    public function setCreatedBy(?User $createdBy): self
    {
        $this->createdBy = $createdBy;

        return $this;
    }

    public function getModifiedBy(): ?User
    {
        return $this->modifiedBy;
    }

    public function setModifiedBy(?User $modifiedBy): self
    {
        $this->modifiedBy = $modifiedBy;

        return $this;
    }

    public function __toString(): string
    {
        return $this->getBrand()->getName().' '.$this->getName().' '.$this->getColor()->getName().' '.$this->getStorage()->getCapacity();
    }

    /**
     * @return Collection|CustomerGroup[]
     */
    public function getCustomerGroup(): Collection
    {
        return $this->customerGroup;
    }

    public function addCustomerGroup(CustomerGroup $customerGroup): self
    {
        if (!$this->customerGroup->contains($customerGroup)) {
            $this->customerGroup[] = $customerGroup;
        }

        return $this;
    }

    public function removeCustomerGroup(CustomerGroup $customerGroup): self
    {
        if ($this->customerGroup->contains($customerGroup)) {
            $this->customerGroup->removeElement($customerGroup);
        }

        return $this;
    }

    /**
     * If manually uploading a file (i.e. not using Symfony Form) ensure an instance
     * of 'UploadedFile' is injected into this setter to trigger the update. If this
     * bundle's configuration parameter 'inject_on_load' is set to 'true' this setter
     * must be able to accept an instance of 'File' as the bundle will inject one here
     * during Doctrine hydration.
     *
     * @param File|\Symfony\Component\HttpFoundation\File\UploadedFile $imageFile
     */
    public function setImageFile(?File $imageFile = null): void
    {
        $this->imageFile = $imageFile;

        if (null !== $imageFile) {
            // It is required that at least one field changes if you are using doctrine
            // otherwise the event listeners won't be called and the file is lost
            $this->dateUpd = new \DateTimeImmutable();
        }
    }

    public function getImageFile(): ?File
    {
        return $this->imageFile;
    }

    public function setImageName(?string $imageName): void
    {
        $this->imageName = $imageName;
    }

    public function getImageName(): ?string
    {
        return $this->imageName;
    }

    public function setImageSize(?int $imageSize): void
    {
        $this->imageSize = $imageSize;
    }

    public function getImageSize(): ?int
    {
        return $this->imageSize;
    }
}

And the Brand Entity :

<?php

namespace App\Entity;

use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Gedmo\Mapping\Annotation as Gedmo;


/**
 * @ORM\Entity(repositoryClass="App\Repository\BrandRepository")
 * @ORM\HasLifecycleCallbacks()
 */
class Brand
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

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

    /**
     * @ORM\Column(type="datetime")
     * @Gedmo\Timestampable(on="create")
     */
    private $dateAdd;

    /**
     * @ORM\Column(type="datetime")
     * @Gedmo\Timestampable(on="create")
     */
    private $dateUpd;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User")
     * @ORM\JoinColumn(nullable=false)
     * @Gedmo\Blameable(on="create")
     */
    private $createdBy;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\User")
     * @ORM\JoinColumn(nullable=false)
     * @Gedmo\Blameable(on="update")
     */
    private $modifiedBy;

    /**
     * @ORM\Column(type="boolean")
     */
    private $isDeleted;

    /**
     * @ORM\Column(type="boolean")
     */
    private $isActive;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Model", mappedBy="Brand")
     */
    private $models;

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

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): self
    {
        $this->name = $name;

        return $this;
    }

    public function getDateAdd(): ?\DateTimeInterface
    {
        return $this->dateAdd;
    }

    public function setDateAdd(\DateTimeInterface $dateAdd): self
    {
        $this->dateAdd = $dateAdd;

        return $this;
    }

    public function getDateUpd(): ?\DateTimeInterface
    {
        return $this->dateUpd;
    }

    public function setDateUpd(\DateTimeInterface $dateUpd): self
    {
        $this->dateUpd = $dateUpd;

        return $this;
    }

    public function getCreatedBy(): ?User
    {
        return $this->createdBy;
    }

    public function setCreatedBy(?User $createdBy): self
    {
        $this->createdBy = $createdBy;

        return $this;
    }

    public function getModifiedBy(): ?User
    {
        return $this->modifiedBy;
    }

    public function setModifiedBy(?User $modifiedBy): self
    {
        $this->modifiedBy = $modifiedBy;

        return $this;
    }

    public function getIsDeleted(): ?bool
    {
        return $this->isDeleted;
    }

    public function setIsDeleted(bool $isDeleted): self
    {
        $this->isDeleted = $isDeleted;

        return $this;
    }

    public function getIsActive(): ?bool
    {
        return $this->isActive;
    }

    public function setIsActive(bool $isActive): self
    {
        $this->isActive = $isActive;

        return $this;
    }

    /**
     * @return Collection|Model[]
     */
    public function getModels(): Collection
    {
        return $this->models;
    }

    public function addModel(Model $model): self
    {
        if (!$this->models->contains($model)) {
            $this->models[] = $model;
            $model->setBrand($this);
        }

        return $this;
    }

    public function removeModel(Model $model): self
    {
        if ($this->models->contains($model)) {
            $this->models->removeElement($model);
            // set the owning side to null (unless already changed)
            if ($model->getBrand() === $this) {
                $model->setBrand(null);
            }
        }

        return $this;
    }

}

Thanks for your help !

Best,

Julien

Answer 1

Thanks for your messages

@Alexandre : I'm going to search this way.

What I've done to achieve my goal : - Add a bidirectionnal relation with inversedby in model and brand entities. This way, I can make that :

$brands = $this->getDoctrine()->getRepository(Brand::class)->findAll();
        $arrayBrands = array();
        $arrayDevicesCount = array();
        foreach($brands as  $brand)
        {

            $devicesCount = 0;
            $models = $brand->getModels();
            foreach ($models as $model)
            {
                $devices = $model->getDevices()->getValues();

                $devices = array_filter($devices, function (Device $device){
                    return $this->isGranted('view', $device);
                });

                $devicesCount+= count($devices);
            }
            if($devicesCount > 0)
            {
                array_push($arrayBrands, $brand->getName());
                array_push($arrayDevicesCount, $devicesCount);
            }
        }

And this is working well !

Answer 2

The voter won't update your query. It is used to check if a user is authorized to access some part of your code.

If you want to have your Devices according to your Voter, you'll need to write the correct query based on you need !