Doctrine QueryBuilder COUNT и избиратели

Question

Doctrine QueryBuilder COUNT и избиратели

У меня есть некоторые сущности, например Device сущность с избирателем, разрешающим текущему пользователю доступ к некоторым Deviceс.

При поиске Devices, для фильтрации я использую array_filter функция, которая работает хорошо.

Но я хочу получить некоторую статистику по моему Device сущность, например количество Devices пользователем Brand.

Мой запрос в порядке:

$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();

У меня есть массив с моими данными.

Но избиратель не претендует.

Если я переключу пользователя на того, у кого нет доступа ко всем Devices, я до сих пор вижу те же числа.

Итак, как мне отфильтровать COUNT запрос с избирателями?

В Device Сущность:

<?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();
    }


}

В Model Сущность:

<?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;
    }
}

И Brand Сущность:

<?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;
    }

}

Спасибо за вашу помощь!

Лучший,

Жюльен

2

symfony doctrine symfony4 query-builder symfony4-voter

Источник

user11966776 10 сен '19 в 23:31

2 ответа

Другие вопросы по тегам symfony doctrine symfony4 query-builder symfony4-voter

user4732903 11 сен '19 в 12:33 2019-09-11 12:33 · Answer 1 · 2019-09-11 12:33

Избиратель не обновляет ваш запрос. Он используется для проверки, авторизован ли пользователь для доступа к какой-либо части вашего кода.

Если вы хотите, чтобы ваши устройства соответствовали вашему избирателю, вам нужно будет написать правильный запрос, основанный на ваших потребностях!

user11966776 11 сен '19 в 19:01 2019-09-11 19:01 · Answer 2 · 2019-09-11 19:01

Спасибо за ваши сообщения

@Alexandre: Я буду искать здесь.

Что я сделал для достижения своей цели: - Добавил двунаправленную связь с inversedbyв сущностях модели и бренда. Таким образом, я могу сделать это:

$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);
            }
        }

И это хорошо работает!