使用Codeigniter活动记录“在距离内”查询 - Using Codeigniter active record for "within distance" query

Using Codeigniter active record for "within distance" query

我试图使用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错误数量:1064
你的SQL语法有错误;查看对应的手册的MySQL服务器版本使用near 'AS distance的正确语法从user_profiles有distance<= 100 ORDER BY distance LIMI' at第5行
SELECT * FROM (locations) WHEREcountry = '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 HAVING distance ' <= 100 ORDER BY distance ' LIMIT 20
文件名:C: ' wamp ' www数据库' mysite '系统' ' DB_driver.php
行号:330

一些事情要注意…我在我的模型中使用了一些where()函数。距离查询应该与其他子句共存。

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

$miles= 20;$this->db->select("*， (3959 * acos(cos(radians($shop_lat)) * cos(radians(lat)) * cos(radians($shop_long)) - radians($shop_long)) + sin(radians($shop_lat)) * sin(radians(lat)))) AS distance"));
$ this -> db ->从("商店");$this->db->having('distance <= ';英里)美元;
$ this -> db -> order_by("距离");
$ this -> db ->限制(20日0);$query = $this->db->get()$result = $query->result();返回结果美元;}