title: DQL Jsor/doctrine-postgis
body: <p>I'm triying to get a calculated value from database where this value depends on an external value. I got the next function:</p>
  <pre><code>public function findAllBy($search, $order, $limit, $offset) {
      $qb = $this-&gt;entityManager
                 -&gt;createQueryBuilder()
                 -&gt;select('a, (ST_DISTANCE(ST_MakePoint(0,0), ST_MakePoint(0,0)) / 1000) as distance')
                 -&gt;from('App\Entity\App\Advertisement', 'a')
                 -&gt;setMaxResult($limit)
                 -&gt;setFirstResult($offset);
  
      for ($order as $key =&gt; $value)
          $qb-&gt;andWhere($key !== &quot;distance&quot; ? &quot;a.{$key}&quot; : &quot;distance&quot;, $value);
  
      return $qb-&gt;getQuery()-&gt;getResult();
  }
  </code></pre>
  <p>This function works, but the problem comes now. Like I said, I need to modify two parameters, one of theme come from outside of this function, that's not a problem, the problem comes with the other one. I the advertisement table I got a column named &quot;coordinate&quot; of type Point (postgis), when I try to substitute one of ST_MakePoint like this:</p>
  <pre><code>(ST_DISTANCE(ST_MakePoint(a.coordinate[0] a.coordinate[1]), ST_MakePoint(0,0)) / 1000) as distance
  </code></pre>
  <p>I got the next error:</p>
  <pre><code>&quot;[Syntax Error] line 0, col 49: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_COMMA, got '['&quot;
  </code></pre>
  <p>I also tried:</p>
  <pre><code>(ST_DISTANCE(a.coordinate::geometry, ST_MakePoint(0,0)) / 1000) as distance
  &quot;[Syntax Error] line 0, col 35: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_COMMA, got ':'&quot;
  </code></pre>
  <p>Other:</p>
  <pre><code>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
  </code></pre>
  <p>Other:</p>
  <pre><code>[Syntax Error] line 0, col 54: Error: Expected Doctrine\\ORM\\Query\\Lexer::T_CLOSE_PARENTHESIS, got ':'&quot;
  (ST_DISTANCE(a.coordinate, ST_MakePoint(0,0)::point) / 1000) as distance
  </code></pre>
  <p>None of them works. I dont how can I use the coordinate column on DQL.
  If that helps, I got another function that works, but it is SQL not DQL, and I dont know how to parse the result as entity. Here it is:</p>
  <pre><code>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 &lt;= 100
  ORDER BY x.distance ASC
  </code></pre>
  <p><a href="https://i.stack.imgur.com/eK7Al.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eK7Al.png" alt="" /></a></p>
  <p>Entity:</p>
  <pre><code>&lt;?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=&quot;app&quot;)
   */
  class Advertisement
  {
      /**
       * @ORM\Id
       * @ORM\GeneratedValue
       * @ORM\Column(type=&quot;integer&quot;)
       */
      private $id;
  
      /**
       * @ORM\ManyToOne(targetEntity=App\Entity\App\Company::class, inversedBy=&quot;advertisements&quot;)
       * @ORM\JoinColumn(nullable=false)
       */
      private $company;
  
      /**
       * @ORM\Column(type=&quot;AdvertisementTypeEnum&quot;)
       */
      private $type;
  
      /**
       * @ORM\Column(type=&quot;string&quot;, length=255)
       */
      private $name;
  
      /**
       * @ORM\Column(type=&quot;string&quot;, length=500)
       */
      private $description;
  
      /**
       * @ORM\Column(type=&quot;float&quot;)
       */
      private $price;
  
      /**
       * @ORM\Column(type=&quot;integer&quot;)
       */
      private $nicheTime;
  
      /**
       * @ORM\Column(type=&quot;boolean&quot;)
       */
      private $enabled;
  
      /**
       * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementComplement::class, mappedBy=&quot;advertisement&quot;)
       */
      private $advertisementComplements;
  
      /**
       * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementTransfer::class, mappedBy=&quot;advertisement&quot;)
       */
      private $advertisementTransfers;
  
      /**
       * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementGraveyard::class, mappedBy=&quot;advertisement&quot;)
       */
      private $advertisementGraveyards;
  
      /**
       * @ORM\OneToMany(targetEntity=App\Entity\App\AdvertisementFuneralParlor::class, mappedBy=&quot;advertisement&quot;)
       */
      private $advertisementFuneralParlors;
  
      /**
       * @ORM\OneToMany(targetEntity=AdvertisementCrematorium::class, mappedBy=&quot;advertisement&quot;)
       */
      private $advertisementCrematoria;
  
      /**
       * @ORM\Column(type=&quot;integer&quot;)
       */
      private $query;
  
      /**
       * @ORM\Column(type=&quot;datetime&quot;)
       */
      private $createAt;
  
      /**
       * @ORM\Column(type=&quot;datetime&quot;)
       */
      private $updatedAt;
  
      /**
       * @ORM\OneToMany(targetEntity=AdvertisementStatistic::class, mappedBy=&quot;advertisement&quot;)
       */
      private $advertisementStatistics;
  
      /**
       * @ORM\Column(type=&quot;date&quot;, nullable=true)
       */
      private $publishDate;
  
      /**
       * @ORM\OneToMany(targetEntity=AdvertisementRate::class, mappedBy=&quot;advertisement&quot;)
       */
      private $advertisementRates;
  
      /**
       * @ORM\Column(type=&quot;integer&quot;, options={&quot;default&quot;:10000})
       */
      private $version;
  
      /**
       * @ORM\OneToMany(targetEntity=Contract::class, mappedBy=&quot;advertisement&quot;, cascade={&quot;persist&quot;}, fetch=&quot;LAZY&quot;)
       */
      private $contracts;
  
      /**
       * @ORM\Column(type=&quot;boolean&quot;)
       */
      private $wake;
  
      /**
       * @ORM\Column(type=&quot;integer&quot;, nullable=true)
       */
      private $wakeTime;
  
      /**
       * @ORM\Column(type=&quot;float&quot;, nullable=true)
       */
      private $wakePrice;
  
      /**
       * @ORM\Column(type=&quot;float&quot;, nullable=true)
       */
      private $kmExtraPrice;
  
      /**
       * @ORM\Column(type=&quot;boolean&quot;, nullable=true)
       */
      private $wakeIncluded;
  
      /**
       * @ORM\Column(type=&quot;datetime_immutable&quot;, nullable=true)
       */
      private $deletedAt;
  
      /**
       * @ORM\ManyToOne(targetEntity=City::class)
       */
      private $city;
  
      /**
       * @ORM\Column(type=&quot;point&quot;, nullable=true)
       */
      private $coordinate;
  
      public function __construct()
      {
          $this-&gt;advertisementComplements = new ArrayCollection();
          $this-&gt;advertisementTransfers = new ArrayCollection();
          $this-&gt;advertisementGraveyards = new ArrayCollection();
          $this-&gt;advertisementFuneralParlors = new ArrayCollection();
          $this-&gt;advertisementCrematoria = new ArrayCollection();
          $this-&gt;createAt = new \DateTime(&quot;now&quot;);
          $this-&gt;updatedAt = new \DateTime(&quot;now&quot;);
          $this-&gt;advertisementStatistics = new ArrayCollection();
          $this-&gt;advertisementRates = new ArrayCollection();
          $this-&gt;contracts = new ArrayCollection();
          $this-&gt;coordinate = $this-&gt;city-&gt;getCoordinate();
      }
  
      public function getId(): ?int
      {
          return $this-&gt;id;
      }
  
      public function getCompany(): ?Company
      {
          return $this-&gt;company;
      }
  
      public function setCompany(?Company $company): self
      {
          $this-&gt;company = $company;
  
          return $this;
      }
  
      public function getType()
      {
          return $this-&gt;type;
      }
  
      public function setType($type): self
      {
          $this-&gt;type = $type;
  
          return $this;
      }
  
      public function getName(): ?string
      {
          return $this-&gt;name;
      }
  
      public function setName(string $name): self
      {
          $this-&gt;name = $name;
  
          return $this;
      }
  
      public function getDescription(): ?string
      {
          return $this-&gt;description;
      }
  
      public function setDescription(string $description): self
      {
          $this-&gt;description = $description;
  
          return $this;
      }
  
      public function getPrice(): ?float
      {
          return $this-&gt;price;
      }
  
      public function setPrice(float $price): self
      {
          $this-&gt;price = $price;
  
          return $this;
      }
  
      public function getNicheTime(): ?int
      {
          return $this-&gt;nicheTime;
      }
  
      public function setNicheTime(?int $nicheTime): self
      {
          $this-&gt;nicheTime = $nicheTime;
  
          return $this;
      }
  
      public function getEnabled(): ?bool
      {
          return $this-&gt;enabled;
      }
  
      public function setEnabled(bool $enabled): self
      {
          $this-&gt;enabled = $enabled;
  
          return $this;
      }
  
      /**
       * @return Collection|AdvertisementComplement[]|null
       */
      public function getAdvertisementComplements(): ?Collection
      {
          return $this-&gt;advertisementComplements;
      }
  
      public function addAdvertisementComplement(AdvertisementComplement $advertisementComplement): self
      {
          if (!$this-&gt;advertisementComplements-&gt;contains($advertisementComplement)) {
              $this-&gt;advertisementComplements[] = $advertisementComplement;
              $advertisementComplement-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeAdvertisementComplement(AdvertisementComplement $advertisementComplement): self
      {
          if ($this-&gt;advertisementComplements-&gt;removeElement($advertisementComplement)) {
              // set the owning side to null (unless already changed)
              if ($advertisementComplement-&gt;getAdvertisement() === $this) {
                  $advertisementComplement-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      /**
       * @return Collection|AdvertisementTransfer[]|null
       */
      public function getAdvertisementTransfers(): ?Collection
      {
          return $this-&gt;advertisementTransfers;
      }
  
      public function addAdvertisementTransfer(AdvertisementTransfer $advertisementTransfer): self
      {
          if (!$this-&gt;advertisementTransfers-&gt;contains($advertisementTransfer)) {
              $this-&gt;advertisementTransfers[] = $advertisementTransfer;
              $advertisementTransfer-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeAdvertisementTransfer(AdvertisementTransfer $advertisementTransfer): self
      {
          if ($this-&gt;advertisementTransfers-&gt;removeElement($advertisementTransfer)) {
              // set the owning side to null (unless already changed)
              if ($advertisementTransfer-&gt;getAdvertisement() === $this) {
                  $advertisementTransfer-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      /**
       * @return Collection|AdvertisementGraveyard[]
       */
      public function getAdvertisementGraveyards(): Collection
      {
          return $this-&gt;advertisementGraveyards;
      }
  
      public function addAdvertisementGraveyard(AdvertisementGraveyard $advertisementGraveyard): self
      {
          if (!$this-&gt;advertisementGraveyards-&gt;contains($advertisementGraveyard)) {
              $this-&gt;advertisementGraveyards[] = $advertisementGraveyard;
              $advertisementGraveyard-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeAdvertisementGraveyard(AdvertisementGraveyard $advertisementGraveyard): self
      {
          if ($this-&gt;advertisementGraveyards-&gt;removeElement($advertisementGraveyard)) {
              // set the owning side to null (unless already changed)
              if ($advertisementGraveyard-&gt;getAdvertisement() === $this) {
                  $advertisementGraveyard-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      /**
       * @return Collection|AdvertisementFuneralParlor[]
       */
      public function getAdvertisementFuneralParlors(): Collection
      {
          return $this-&gt;advertisementFuneralParlors;
      }
  
      public function addAdvertisementFuneralParlor(AdvertisementFuneralParlor $advertisementFuneralParlor): self
      {
          if (!$this-&gt;advertisementFuneralParlors-&gt;contains($advertisementFuneralParlor)) {
              $this-&gt;advertisementFuneralParlors[] = $advertisementFuneralParlor;
              $advertisementFuneralParlor-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeAdvertisementFuneralParlor(AdvertisementFuneralParlor $advertisementFuneralParlor): self
      {
          if ($this-&gt;advertisementFuneralParlors-&gt;removeElement($advertisementFuneralParlor)) {
              // set the owning side to null (unless already changed)
              if ($advertisementFuneralParlor-&gt;getAdvertisement() === $this) {
                  $advertisementFuneralParlor-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      public function getQuery(): ?int
      {
          return $this-&gt;query;
      }
  
      public function setQuery(int $query): self
      {
          $this-&gt;query = $query;
  
          return $this;
      }
  
      /**
       * @return Collection|AdvertisementCrematorium[]
       */
      public function getAdvertisementCrematoria(): Collection
      {
          return $this-&gt;advertisementCrematoria;
      }
  
      public function addAdvertisementCrematorium(AdvertisementCrematorium $advertisementCrematorium): self
      {
          if (!$this-&gt;advertisementCrematoria-&gt;contains($advertisementCrematorium)) {
              $this-&gt;advertisementCrematoria[] = $advertisementCrematorium;
              $advertisementCrematorium-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeAdvertisementCrematorium(AdvertisementCrematorium $advertisementCrematorium): self
      {
          if ($this-&gt;advertisementCrematoria-&gt;removeElement($advertisementCrematorium)) {
              // set the owning side to null (unless already changed)
              if ($advertisementCrematorium-&gt;getAdvertisement() === $this) {
                  $advertisementCrematorium-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      public function getCreateAt(): ?\DateTimeInterface
      {
          return $this-&gt;createAt;
      }
  
      public function getCreateAtNormalized(): string
      {
          return $this-&gt;createAt-&gt;format(&quot;Y-m-d H:i:s&quot;);
      }
  
      public function setCreateAt(): self
      {
          $this-&gt;createAt = new \DateTime(&quot;now&quot;);
  
          return $this;
      }
  
      public function getUpdatedAt(): ?\DateTimeInterface
      {
          return $this-&gt;updatedAt;
      }
  
      public function getUpdateAtNormalized(): string
      {
          return $this-&gt;updatedAt-&gt;format(&quot;Y-m-d H:i:s&quot;);
      }
  
      public function setUpdatedAt(): self
      {
          $this-&gt;updatedAt = new \DateTime(&quot;now&quot;);
  
          return $this;
      }
  
      /**
       * @return Collection|AdvertisementStatistic[]
       */
      public function getAdvertisementStatistics(): Collection
      {
          return $this-&gt;advertisementStatistics;
      }
  
      public function addAdvertisementStatistic(AdvertisementStatistic $advertisementStatistic): self
      {
          if (!$this-&gt;advertisementStatistics-&gt;contains($advertisementStatistic)) {
              $this-&gt;advertisementStatistics[] = $advertisementStatistic;
              $advertisementStatistic-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeAdvertisementStatistic(AdvertisementStatistic $advertisementStatistic): self
      {
          if ($this-&gt;advertisementStatistics-&gt;removeElement($advertisementStatistic)) {
              // set the owning side to null (unless already changed)
              if ($advertisementStatistic-&gt;getAdvertisement() === $this) {
                  $advertisementStatistic-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      public function getPublishDate(): ?\DateTimeInterface
      {
          return $this-&gt;publishDate;
      }
  
      public function setPublishDate(): self
      {
          $this-&gt;publishDate = new \DateTime(&quot;now&quot;);
          return $this;
      }
  
      public function clearPublishDate(): self
      {
          $this-&gt;publishDate = null;
          return $this;
      }
  
      /**
       * @return Collection|AdvertisementRate[]
       */
      public function getAdvertisementRates(): Collection
      {
          return $this-&gt;advertisementRates;
      }
  
      public function addAdvertisementRate(AdvertisementRate $advertisementRate): self
      {
          if (!$this-&gt;advertisementRates-&gt;contains($advertisementRate)) {
              $this-&gt;advertisementRates[] = $advertisementRate;
              $advertisementRate-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeAdvertisementRate(AdvertisementRate $advertisementRate): self
      {
          if ($this-&gt;advertisementRates-&gt;removeElement($advertisementRate)) {
              // set the owning side to null (unless already changed)
              if ($advertisementRate-&gt;getAdvertisement() === $this) {
                  $advertisementRate-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      public function getVersion(): int
      {
          return $this-&gt;version;
      }
  
      public function getStringVersion(): string
      {
          $v = $this-&gt;getVersion();
          $result = &quot;&quot;;
  
          while ($v &gt; 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-&gt;version = $version;
          return $this;
      }
  
      /**
       * @return Collection|Contract[]
       */
      public function getContracts(): Collection
      {
          return $this-&gt;contracts;
      }
  
      public function addContract(Contract $contract): self
      {
          if (!$this-&gt;contracts-&gt;contains($contract)) {
              $this-&gt;contracts[] = $contract;
              $contract-&gt;setAdvertisement($this);
          }
  
          return $this;
      }
  
      public function removeContract(Contract $contract): self
      {
          if ($this-&gt;contracts-&gt;removeElement($contract)) {
              // set the owning side to null (unless already changed)
              if ($contract-&gt;getAdvertisement() === $this) {
                  $contract-&gt;setAdvertisement(null);
              }
          }
  
          return $this;
      }
  
      public function getWake(): bool
      {
          return $this-&gt;wake;
      }
  
      public function setWake(bool $wake): self
      {
          $this-&gt;wake = $wake;
  
          return $this;
      }
  
      public function getWakeTime(): ?int
      {
          return $this-&gt;wakeTime;
      }
  
      public function setWakeTime(?int $wakeTime): self
      {
          $this-&gt;wakeTime = $wakeTime;
  
          return $this;
      }
  
      public function getWakePrice(): ?float
      {
          return $this-&gt;wakePrice;
      }
  
      public function setWakePrice(?float $wakePrice): self
      {
          $this-&gt;wakePrice = $wakePrice;
  
          return $this;
      }
  
      public function getKmExtraPrice(): ?float
      {
          return $this-&gt;kmExtraPrice;
      }
  
      public function setKmExtraPrice(?float $kmExtraPrice): self
      {
          $this-&gt;kmExtraPrice = $kmExtraPrice;
  
          return $this;
      }
  
      public function getWakeIncluded(): ?bool
      {
          return $this-&gt;wakeIncluded;
      }
  
      private function getJsonRates(): array
      {
          $size = sizeof($this-&gt;advertisementRates);
          if ($size == 0) return [];
          $average = 0.0;
          $jsonRates = [];
          foreach ($this-&gt;advertisementRates as $advertisementRate) {
              array_push($jsonRates, $advertisementRate-&gt;toJsonArray());
              $average += $advertisementRate-&gt;getScore();
          }
  
          $average /= $size;
          return [&quot;average&quot; =&gt; $average, &quot;rates&quot; =&gt; $jsonRates, &quot;size&quot; =&gt; $size];
      }
  
      public function getDeletedAt(): ?\DateTimeImmutable
      {
          return $this-&gt;deletedAt;
      }
  
      public function setDeletedAt(?\DateTimeImmutable $deletedAt): self
      {
          $this-&gt;deletedAt = $deletedAt;
  
          return $this;
      }
  
      public function setWakeIncluded(?bool $wakeIncluded): self
      {
          $this-&gt;wakeIncluded = $wakeIncluded;
  
          return $this;
      }
  
      public function getCity(): ?City
      {
          return $this-&gt;city;
      }
  
      public function setCity(?City $city): self
      {
          $this-&gt;city = $city;
  
          return $this;
      }
  
      public function getDistance(Point $destiny)
      {
          dump($this-&gt;tools);
          return 0;
  //        return $this-&gt;tools-&gt;getDistanceBetweenPoint($this-&gt;coordinate, $destiny);
      }
  
      public function toJsonArray(): array
      {
          $publishDate = $this-&gt;getPublishDate();
          $rates = $this-&gt;getJsonRates();
  
          return [
              &quot;id&quot; =&gt; $this-&gt;id,
              &quot;companyId&quot; =&gt; $this-&gt;company-&gt;getId(),
              &quot;name&quot; =&gt; $this-&gt;name,
              &quot;description&quot; =&gt; $this-&gt;description,
              &quot;type&quot; =&gt; $this-&gt;type,
              &quot;basePrice&quot; =&gt; $this-&gt;price,
              &quot;query&quot; =&gt; $this-&gt;query,
              &quot;updateAt&quot; =&gt; $this-&gt;getUpdateAtNormalized(),
              &quot;publishDate&quot; =&gt; !is_null($publishDate) ? $publishDate-&gt;format(&quot;d-m-Y&quot;) : null,
              &quot;enable&quot; =&gt; $this-&gt;enabled,
              &quot;version&quot; =&gt; $this-&gt;getStringVersion(),
              &quot;wake&quot; =&gt; $this-&gt;wake,
              &quot;wakeIncluded&quot; =&gt; $this-&gt;wakeIncluded,
              &quot;wakePrice&quot; =&gt; $this-&gt;wakePrice,
              &quot;wakeTime&quot; =&gt; $this-&gt;wakeTime,
              &quot;kmExtraPrice&quot; =&gt; $this-&gt;kmExtraPrice,
              &quot;rates&quot; =&gt; empty($rates) ?
                  null :
                  [
                      &quot;average&quot; =&gt; $rates['average'],
                      &quot;rates&quot; =&gt; $rates['rates'],
                      &quot;ratesCount&quot; =&gt; $rates['size']
                  ],
          ];
      }
  }
  </code></pre>