im trying to build a location based event search in PHP + MySQL (Using Laravel and its Eloquent ORM)
This is the query I am using:
select
events.*,
( 3959 * acos( cos( radians(50.5) ) * cos( radians( addresses.latitude ) ) * cos( radians( addresses.longitude ) - radians(9.50) ) + sin( radians(50.5) ) * sin( radians( addresses.latitude ) ) ) ) AS distance
from
`events` inner join `addresses` on `events`.`address_id` = `addresses`.`id`
having
`distance` <= 10
order by
`id` desc limit 15 offset 0
Im wondering why this error pops up, even though the distance column is in the "select statement".
"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from `events` inner join `addresses` on `events`.`address_id` = `addresses`.`id` having `distance` <= 10 order by `id` desc)"
This is the PHP Code I use to add the scope to the base query:
$selectDistance =
'( 3959 * acos( cos( radians(' . $latitude . ') ) ' .
'* cos( radians( addresses.latitude ) ) ' .
'* cos( radians( addresses.longitude ) - radians(' . $longitude . ') ) ' .
'+ sin( radians(' . $latitude . ') ) ' .
'* sin( radians( addresses.latitude ) ) ) ) AS distance';
$query->select(DB::raw('events.*, ' . $selectDistance));
$query->join('addresses', 'events.address_id', '=', 'addresses.id');
$query->having('distance', '<=', $km);
Thank you very much :)