php - 使用 Codeigniter 事件记录进行 "within distance"查询

Question

我正在尝试使用 ActiveRecord 在 CI 中创建以下查询

SELECT *, 
   ( 3959 * acos( cos( radians($lat) ) 
   * cos( radians( lat ) ) 
   * cos( radians( lng ) - radians($lng) ) 
   + sin( radians($lat) ) 
   * sin( radians( lat ) ) ) ) AS distance 
FROM locations 
HAVING distance <= $miles 
ORDER BY distance 
LIMIT 0, 20

我试过了

$where = "( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM locations";        
$this->db->where($where);                           
$this->db->having('distance <= ' . $miles);                     
$this->db->order_by('distance');                    
$this->db->limit(20, 0);

问题(我认为)是我已经通过 $query = $this->db->get('locations'); 告诉我从哪个表获取数据在我的模型的末尾。所以我收到以下错误:

A Database Error Occurred Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS distance FROM user_profiles HAVING distance <= 100 ORDER BY distance LIMI' at line 5

SELECT * FROM (locations) WHERE country = 'US' AND tags = 'technology' AND
( 3959 * acos( cos(
radians(25.9331488) ) * cos( radians(
lat ) ) * cos( radians( lng ) -
radians(-80.1625463) ) + sin(
radians(25.9331488) ) * sin( radians(
lat ) ) ) ) AS distance FROM
locations HAVINGdistance<= 100
ORDER BYdistance` LIMIT 20

Filename: C:\wamp\www\mysite\system\database\DB_driver.php

Line Number: 330

一些注意事项.. 我正在使用一些 where()在我的模型中起作用。距离查询应该与其他子句共存。

Answer 1

您在 SQL 中将 distance 计算设置为 WHERE。您希望它在您的 SELECT 中。没有测试，但尝试:

$this->db->select("*, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance");                         
$this->db->having('distance <= ' . $miles);                     
$this->db->order_by('distance');                    
$this->db->limit(20, 0);