PDO for DOCTRINE - Haversine formula

1

I need to transfer the query in PDO below to DOCTRINE:

   $query = $con->query('SELECT *,
            ( 6371 * acos( cos( radians('.$latitude.') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('.$longitude.') ) + sin( radians('.$latitude.') ) * sin( radians( latitude ) ) ) ) AS distance, city_name
            FROM event INNER JOIN city ON event_city_id = city_id
            WHERE event_sin_active = 1
            ORDER BY distance');

So far I have been able to rewrite the part of this query in the doctrine. The problem appears when you insert the Haversine formula into the SELECT as the questions: SQL query using Haversine in Doctrine and How to calculate distance using latitude and longitude?

The two tables in the database used are Event and City

    CREATE TABLE 'event' (
  'event_id' int(11) NOT NULL,
  'event_name' varchar(45) NOT NULL,
  'event_image' varchar(45) DEFAULT NULL,
  'event_city_id' int(11) NOT NULL,
  'latitude' double DEFAULT NULL,
  'longitude' double DEFAULT NULL,
  'event_tax_service' decimal(10,2) DEFAULT NULL,
  'event_user_id' int(11) NOT NULL,
  'event_sin_active' tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY ('event_id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



 CREATE TABLE 'city' (
  'city_id' int(11) NOT NULL AUTO_INCREMENT,
  'city_name' varchar(100) NOT NULL,
  PRIMARY KEY ('city_id')
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

When you try to run the query below, the system returns a blank screen and does not tell you which exception. What is the correct way to write this query?

$qb = $entityManager->createQueryBuilder();
        $qb->select("e, e.eventCityId,c.cityName, (6371 * ACOS(SIN(RADIANS($latitude)) * SIN(RADIANS(e.latitude)) + COS(RADIANS($latitude)) * COS(RADIANS(e.latitude)) * COS(RADIANS(e.longitude) - RADIANS($longitude)))) as distance")
            ->from('\App\Entity\Event', 'e')
            ->innerJoin('\App\Entity\City', 'c', Join::WITH, $qb->expr()->eq('e.eventCityId', 'c.cityId'))
            ->where('e.eventSinActive = 1')
            ->orderBy('distance', 'ASC');
    
asked by anonymous 19.11.2016 / 01:15

0 answers