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');