DQL Jsor/doctrine-postgis

Question

我正在尝试从数据库获取计算值，其中该值取决于外部值。我得到了下一个函数：

public function findAllBy($search, $order, $limit, $offset) {
    $qb = $this->entityManager
               ->createQueryBuilder()
               ->select('a, (ST_DISTANCE(ST_MakePoint(0,0), ST_MakePoint(0,0)) / 1000) as distance')
               ->from('App\Entity\App\Advertisement', 'a')
               ->setMaxResult($limit)
               ->setFirstResult($offset);

    for ($order as $key => $value)
        $qb->andWhere($key !== "distance" ? "a.{$key}" : "distance", $value);

    return $qb->getQuery()->getResult();
}

这个函数可以工作，但是现在问题来了。就像我说的，我需要修改两个参数，其中一个主题来自这个函数的外部，这不是问题，问题来自另一个。在广告表中，我得到了一列名为“坐标”的 Point 类型（postgis），当我尝试像这样替换 ST_MakePoint 之一时：

(ST_DISTANCE(ST_MakePoint(a.coordinate[0] a.coordinate[1]), ST_MakePoint(0,0)) / 1000) as distance

我收到下一个错误：

"[Syntax Error] line 0, col 49: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_COMMA, got '['"

我也尝试过：

(ST_DISTANCE(a.coordinate::geometry, ST_MakePoint(0,0)) / 1000) as distance
"[Syntax Error] line 0, col 35: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_COMMA, got ':'"

其他：

ERROR:  function st_distance(point, geometry) does not exist
(ST_DISTANCE(a.coordinate, ST_MakePoint(0,0)) / 1000) as distance // Error coordinate not a geometry

其他：

[Syntax Error] line 0, col 54: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_CLOSE_PARENTHESIS, got ':'"
(ST_DISTANCE(a.coordinate, ST_MakePoint(0,0)::point) / 1000) as distance

它们都不起作用。我不知道如何使用 DQL 上的坐标列。 如果这有帮助，我得到了另一个有效的函数，但它是 SQL 而不是 DQL，而且我不知道如何将结果解析为实体。这是：

SELECT x.id
FROM (
    SELECT
        g.id,
        (ST_DISTANCE(ST_MakePoint(g.coordinate[0], g.coordinate[1])::geography, ST_MakePoint($point)::geography) / 1000) as distance
    FROM app.graveyard g
    ) AS x
WHERE x.distance <= 100
ORDER BY x.distance ASC

实体：

<?php

namespace App\Entity\App;

use App\DBAL\Types\Geolocation\Point;
use App\Repository\App\AdvertisementRepository;
use App\Tools\Tools;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Serializer\Annotation\MaxDepth;

/**
 * @ORM\Entity(repositoryClass=AdvertisementRepository::class)
 * @ORM\Table(schema="app")
 */
class Advertisement
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity=App\Entity\App\Company::class, inversedBy="advertisements")
     * @ORM\JoinColumn(nullable=false)
     */
    private $company;

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

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

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

    /**
     * @ORM\Column(type="float")
     */
    private $price;

    /**
     * @ORM\Column(type="integer")
     */
    private $nicheTime;

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

    /**
     * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementComplement::class, mappedBy="advertisement")
     */
    private $advertisementComplements;

    /**
     * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementTransfer::class, mappedBy="advertisement")
     */
    private $advertisementTransfers;

    /**
     * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementGraveyard::class, mappedBy="advertisement")
     */
    private $advertisementGraveyards;

    /**
     * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementFuneralParlor::class, mappedBy="advertisement")
     */
    private $advertisementFuneralParlors;

    /**
     * @ORM\OneToMany(targetEntity=AdvertisementCrematorium::class, mappedBy="advertisement")
     */
    private $advertisementCrematoria;

    /**
     * @ORM\Column(type="integer")
     */
    private $query;

    /**
     * @ORM\Column(type="datetime")
     */
    private $createAt;

    /**
     * @ORM\Column(type="datetime")
     */
    private $updatedAt;

    /**
     * @ORM\OneToMany(targetEntity=AdvertisementStatistic::class, mappedBy="advertisement")
     */
    private $advertisementStatistics;

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

    /**
     * @ORM\OneToMany(targetEntity=AdvertisementRate::class, mappedBy="advertisement")
     */
    private $advertisementRates;

    /**
     * @ORM\Column(type="integer", options={"default":10000})
     */
    private $version;

    /**
     * @ORM\OneToMany(targetEntity=Contract::class, mappedBy="advertisement", cascade={"persist"}, fetch="LAZY")
     */
    private $contracts;

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

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

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

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

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

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

    /**
     * @ORM\ManyToOne(targetEntity=City::class)
     */
    private $city;

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

    public function __construct()
    {
        $this->advertisementComplements = new ArrayCollection();
        $this->advertisementTransfers = new ArrayCollection();
        $this->advertisementGraveyards = new ArrayCollection();
        $this->advertisementFuneralParlors = new ArrayCollection();
        $this->advertisementCrematoria = new ArrayCollection();
        $this->createAt = new \DateTime("now");
        $this->updatedAt = new \DateTime("now");
        $this->advertisementStatistics = new ArrayCollection();
        $this->advertisementRates = new ArrayCollection();
        $this->contracts = new ArrayCollection();
        $this->coordinate = $this->city->getCoordinate();
    }

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

    public function getCompany(): ?Company
    {
        return $this->company;
    }

    public function setCompany(?Company $company): self
    {
        $this->company = $company;

        return $this;
    }

    public function getType()
    {
        return $this->type;
    }

    public function setType($type): self
    {
        $this->type = $type;

        return $this;
    }

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

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

        return $this;
    }

    public function getDescription(): ?string
    {
        return $this->description;
    }

    public function setDescription(string $description): self
    {
        $this->description = $description;

        return $this;
    }

    public function getPrice(): ?float
    {
        return $this->price;
    }

    public function setPrice(float $price): self
    {
        $this->price = $price;

        return $this;
    }

    public function getNicheTime(): ?int
    {
        return $this->nicheTime;
    }

    public function setNicheTime(?int $nicheTime): self
    {
        $this->nicheTime = $nicheTime;

        return $this;
    }

    public function getEnabled(): ?bool
    {
        return $this->enabled;
    }

    public function setEnabled(bool $enabled): self
    {
        $this->enabled = $enabled;

        return $this;
    }

    /**
     * @return Collection|AdvertisementComplement[]|null
     */
    public function getAdvertisementComplements(): ?Collection
    {
        return $this->advertisementComplements;
    }

    public function addAdvertisementComplement(AdvertisementComplement $advertisementComplement): self
    {
        if (!$this->advertisementComplements->contains($advertisementComplement)) {
            $this->advertisementComplements[] = $advertisementComplement;
            $advertisementComplement->setAdvertisement($this);
        }

        return $this;
    }

    public function removeAdvertisementComplement(AdvertisementComplement $advertisementComplement): self
    {
        if ($this->advertisementComplements->removeElement($advertisementComplement)) {
            // set the owning side to null (unless already changed)
            if ($advertisementComplement->getAdvertisement() === $this) {
                $advertisementComplement->setAdvertisement(null);
            }
        }

        return $this;
    }

    /**
     * @return Collection|AdvertisementTransfer[]|null
     */
    public function getAdvertisementTransfers(): ?Collection
    {
        return $this->advertisementTransfers;
    }

    public function addAdvertisementTransfer(AdvertisementTransfer $advertisementTransfer): self
    {
        if (!$this->advertisementTransfers->contains($advertisementTransfer)) {
            $this->advertisementTransfers[] = $advertisementTransfer;
            $advertisementTransfer->setAdvertisement($this);
        }

        return $this;
    }

    public function removeAdvertisementTransfer(AdvertisementTransfer $advertisementTransfer): self
    {
        if ($this->advertisementTransfers->removeElement($advertisementTransfer)) {
            // set the owning side to null (unless already changed)
            if ($advertisementTransfer->getAdvertisement() === $this) {
                $advertisementTransfer->setAdvertisement(null);
            }
        }

        return $this;
    }

    /**
     * @return Collection|AdvertisementGraveyard[]
     */
    public function getAdvertisementGraveyards(): Collection
    {
        return $this->advertisementGraveyards;
    }

    public function addAdvertisementGraveyard(AdvertisementGraveyard $advertisementGraveyard): self
    {
        if (!$this->advertisementGraveyards->contains($advertisementGraveyard)) {
            $this->advertisementGraveyards[] = $advertisementGraveyard;
            $advertisementGraveyard->setAdvertisement($this);
        }

        return $this;
    }

    public function removeAdvertisementGraveyard(AdvertisementGraveyard $advertisementGraveyard): self
    {
        if ($this->advertisementGraveyards->removeElement($advertisementGraveyard)) {
            // set the owning side to null (unless already changed)
            if ($advertisementGraveyard->getAdvertisement() === $this) {
                $advertisementGraveyard->setAdvertisement(null);
            }
        }

        return $this;
    }

    /**
     * @return Collection|AdvertisementFuneralParlor[]
     */
    public function getAdvertisementFuneralParlors(): Collection
    {
        return $this->advertisementFuneralParlors;
    }

    public function addAdvertisementFuneralParlor(AdvertisementFuneralParlor $advertisementFuneralParlor): self
    {
        if (!$this->advertisementFuneralParlors->contains($advertisementFuneralParlor)) {
            $this->advertisementFuneralParlors[] = $advertisementFuneralParlor;
            $advertisementFuneralParlor->setAdvertisement($this);
        }

        return $this;
    }

    public function removeAdvertisementFuneralParlor(AdvertisementFuneralParlor $advertisementFuneralParlor): self
    {
        if ($this->advertisementFuneralParlors->removeElement($advertisementFuneralParlor)) {
            // set the owning side to null (unless already changed)
            if ($advertisementFuneralParlor->getAdvertisement() === $this) {
                $advertisementFuneralParlor->setAdvertisement(null);
            }
        }

        return $this;
    }

    public function getQuery(): ?int
    {
        return $this->query;
    }

    public function setQuery(int $query): self
    {
        $this->query = $query;

        return $this;
    }

    /**
     * @return Collection|AdvertisementCrematorium[]
     */
    public function getAdvertisementCrematoria(): Collection
    {
        return $this->advertisementCrematoria;
    }

    public function addAdvertisementCrematorium(AdvertisementCrematorium $advertisementCrematorium): self
    {
        if (!$this->advertisementCrematoria->contains($advertisementCrematorium)) {
            $this->advertisementCrematoria[] = $advertisementCrematorium;
            $advertisementCrematorium->setAdvertisement($this);
        }

        return $this;
    }

    public function removeAdvertisementCrematorium(AdvertisementCrematorium $advertisementCrematorium): self
    {
        if ($this->advertisementCrematoria->removeElement($advertisementCrematorium)) {
            // set the owning side to null (unless already changed)
            if ($advertisementCrematorium->getAdvertisement() === $this) {
                $advertisementCrematorium->setAdvertisement(null);
            }
        }

        return $this;
    }

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

    public function getCreateAtNormalized(): string
    {
        return $this->createAt->format("Y-m-d H:i:s");
    }

    public function setCreateAt(): self
    {
        $this->createAt = new \DateTime("now");

        return $this;
    }

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

    public function getUpdateAtNormalized(): string
    {
        return $this->updatedAt->format("Y-m-d H:i:s");
    }

    public function setUpdatedAt(): self
    {
        $this->updatedAt = new \DateTime("now");

        return $this;
    }

    /**
     * @return Collection|AdvertisementStatistic[]
     */
    public function getAdvertisementStatistics(): Collection
    {
        return $this->advertisementStatistics;
    }

    public function addAdvertisementStatistic(AdvertisementStatistic $advertisementStatistic): self
    {
        if (!$this->advertisementStatistics->contains($advertisementStatistic)) {
            $this->advertisementStatistics[] = $advertisementStatistic;
            $advertisementStatistic->setAdvertisement($this);
        }

        return $this;
    }

    public function removeAdvertisementStatistic(AdvertisementStatistic $advertisementStatistic): self
    {
        if ($this->advertisementStatistics->removeElement($advertisementStatistic)) {
            // set the owning side to null (unless already changed)
            if ($advertisementStatistic->getAdvertisement() === $this) {
                $advertisementStatistic->setAdvertisement(null);
            }
        }

        return $this;
    }

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

    public function setPublishDate(): self
    {
        $this->publishDate = new \DateTime("now");
        return $this;
    }

    public function clearPublishDate(): self
    {
        $this->publishDate = null;
        return $this;
    }

    /**
     * @return Collection|AdvertisementRate[]
     */
    public function getAdvertisementRates(): Collection
    {
        return $this->advertisementRates;
    }

    public function addAdvertisementRate(AdvertisementRate $advertisementRate): self
    {
        if (!$this->advertisementRates->contains($advertisementRate)) {
            $this->advertisementRates[] = $advertisementRate;
            $advertisementRate->setAdvertisement($this);
        }

        return $this;
    }

    public function removeAdvertisementRate(AdvertisementRate $advertisementRate): self
    {
        if ($this->advertisementRates->removeElement($advertisementRate)) {
            // set the owning side to null (unless already changed)
            if ($advertisementRate->getAdvertisement() === $this) {
                $advertisementRate->setAdvertisement(null);
            }
        }

        return $this;
    }

    public function getVersion(): int
    {
        return $this->version;
    }

    public function getStringVersion(): string
    {
        $v = $this->getVersion();
        $result = "";

        while ($v > 0) {
            $result = ((strlen($result) + 2) % 3) == 0 ? '.' . $v %10 . $result : $v %10 . $result;
            $v = (int)($v / 10);
        }

        return $result;
    }

    public function setVersion(int $version): self
    {
        $this->version = $version;
        return $this;
    }

    /**
     * @return Collection|Contract[]
     */
    public function getContracts(): Collection
    {
        return $this->contracts;
    }

    public function addContract(Contract $contract): self
    {
        if (!$this->contracts->contains($contract)) {
            $this->contracts[] = $contract;
            $contract->setAdvertisement($this);
        }

        return $this;
    }

    public function removeContract(Contract $contract): self
    {
        if ($this->contracts->removeElement($contract)) {
            // set the owning side to null (unless already changed)
            if ($contract->getAdvertisement() === $this) {
                $contract->setAdvertisement(null);
            }
        }

        return $this;
    }

    public function getWake(): bool
    {
        return $this->wake;
    }

    public function setWake(bool $wake): self
    {
        $this->wake = $wake;

        return $this;
    }

    public function getWakeTime(): ?int
    {
        return $this->wakeTime;
    }

    public function setWakeTime(?int $wakeTime): self
    {
        $this->wakeTime = $wakeTime;

        return $this;
    }

    public function getWakePrice(): ?float
    {
        return $this->wakePrice;
    }

    public function setWakePrice(?float $wakePrice): self
    {
        $this->wakePrice = $wakePrice;

        return $this;
    }

    public function getKmExtraPrice(): ?float
    {
        return $this->kmExtraPrice;
    }

    public function setKmExtraPrice(?float $kmExtraPrice): self
    {
        $this->kmExtraPrice = $kmExtraPrice;

        return $this;
    }

    public function getWakeIncluded(): ?bool
    {
        return $this->wakeIncluded;
    }

    private function getJsonRates(): array
    {
        $size = sizeof($this->advertisementRates);
        if ($size == 0) return [];
        $average = 0.0;
        $jsonRates = [];
        foreach ($this->advertisementRates as $advertisementRate) {
            array_push($jsonRates, $advertisementRate->toJsonArray());
            $average += $advertisementRate->getScore();
        }

        $average /= $size;
        return ["average" => $average, "rates" => $jsonRates, "size" => $size];
    }

    public function getDeletedAt(): ?\DateTimeImmutable
    {
        return $this->deletedAt;
    }

    public function setDeletedAt(?\DateTimeImmutable $deletedAt): self
    {
        $this->deletedAt = $deletedAt;

        return $this;
    }

    public function setWakeIncluded(?bool $wakeIncluded): self
    {
        $this->wakeIncluded = $wakeIncluded;

        return $this;
    }

    public function getCity(): ?City
    {
        return $this->city;
    }

    public function setCity(?City $city): self
    {
        $this->city = $city;

        return $this;
    }

    public function getDistance(Point $destiny)
    {
        dump($this->tools);
        return 0;
//        return $this->tools->getDistanceBetweenPoint($this->coordinate, $destiny);
    }

    public function toJsonArray(): array
    {
        $publishDate = $this->getPublishDate();
        $rates = $this->getJsonRates();

        return [
            "id" => $this->id,
            "companyId" => $this->company->getId(),
            "name" => $this->name,
            "description" => $this->description,
            "type" => $this->type,
            "basePrice" => $this->price,
            "query" => $this->query,
            "updateAt" => $this->getUpdateAtNormalized(),
            "publishDate" => !is_null($publishDate) ? $publishDate->format("d-m-Y") : null,
            "enable" => $this->enabled,
            "version" => $this->getStringVersion(),
            "wake" => $this->wake,
            "wakeIncluded" => $this->wakeIncluded,
            "wakePrice" => $this->wakePrice,
            "wakeTime" => $this->wakeTime,
            "kmExtraPrice" => $this->kmExtraPrice,
            "rates" => empty($rates) ?
                null :
                [
                    "average" => $rates['average'],
                    "rates" => $rates['rates'],
                    "ratesCount" => $rates['size']
                ],
        ];
    }
}