Map SQL alias to class attribute

Using Neos & Flow
1

I have a custom sql query to fetch addresses from a database table with coordinates:

    /**
     * @param float $latitude
     * @param float $longitude
     * @param float $distance
     * @return Address[]
     */
    public function findByCoordinatesAndDistance(float $latitude, float $longitude, float $distance)
    {
        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata(Address::class, 'a');

        return $this->getEntityManager()->createNativeQuery('
            SELECT
                *,
                (
                    (
                        ACOS(
                            SIN(? * PI() / 180)
                            * SIN(latitude * PI() / 180)
                            + COS(? * PI() / 180)
                            * COS(latitude * PI() / 180)
                            * COS((? - longitude) * PI() / 180)
                        ) * 180 / PI()
                    ) * 60 * 1.1515
                ) AS distance
            FROM
                lunor_storelocator_domain_model_address
            HAVING
                distance < ?
            ORDER BY
                distance ASC',
            $rsm
        )
            ->setParameter(1, $latitude)
            ->setParameter(2, $latitude)
            ->setParameter(3, $longitude)
            ->setParameter(4, $distance)
            ->getResult();
    }

the model of the database table:

    use Neos\Flow\Annotations as Flow;
    
    /**
     * @Flow\Entity
     */
    class Address
    {
        /**
         * @var float
         * @Flow\Transient()
         */
        protected $distance = 0.0;

        /**
         * @return float
         */
        public function getDistance()
        {
            return $this->distance;
        }
    
        /**
         * @param float $distance
         * @return self
         */
        public function setDistance($distance): self
        {
            $this->distance = $distance;
            return $this;
        }

Problem: when “@Flow\Transient()” is in the annotation of the property the column will be not created in the migration (correct), but my distance (… AS distance,) will be not mapped with it (wrong) (getDistance returns no value).

when I have no “@Flow\Transient()” in the annotation the column will be generated by doctrine:migrategenerate and doctrine:migrate (wrong), but the alias will be mapped to the property (correct) (getDistance returns a value).

What I would like to have:

  • no own column in the database table with “distance” (it should be just an alias)
  • getDistance should return the calculated distance

How can I solve that?

2

As you already have a ResultSetMappingBuilder I think you just need to adapt that to include your custom field. The default mapper will only map the defined entity properties. But checkout the doctrine documentation, you should be able to add the distance field to the mapper and that’s it. :slight_smile: