mysql - CakePhp根据经纬度从数据库获取结果

Question

您好，我有这个原始查询，它为我提供了其位置距离的结果

SELECT *,( 3959 * ACOS( COS( RADIANS(42.290763) ) * COS( RADIANS( location.lat ) ) 
   * COS( RADIANS(location.long) - RADIANS(-71.35368)) + SIN(RADIANS(42.290763)) 
   * SIN( RADIANS(location.lat)))) AS distance 
FROM `gig_post`
JOIN user_info  ON `gig_post`.user_id = user_info.user_id
JOIN location
ON location.`gig_post_id`=`gig_post`.gig_post_id
ORDER BY distance;

这是我的 cakephp 查询，它从多个表中获取结果。

public function getAllGigPosts(){
        $this->Behaviors->attach('Containable');
        return $this->find('all', array(

            'contain' => array(
              'User','UserInfo', 'GigPostAndCategory.Category','Location','GigPostAndCalender'

            ),
              'conditions' => array(
                'GigPost.active' => 1

                 //'OrderGigPost.request' => 0
            ),
            'order' => 'GigPost.gig_post_id DESC',

            'recursive' => 0
        ));
    }

现在，在这个 Cakephp 查询中，我想要实现原始查询，该查询具有在查询中给出距离的公式。我不知道如何在当前的 cakephp 包含查询中插入原始查询。

Answer 1

基于此link :

在您的 AppModel.php 中:

public function getAllGigPosts($options = array()) {
          $defaults = array(
            'latitude'  => 42.290763,
            'longitude' => -71.35368,
          );
          $options = Set::merge($defaults, $options);

          $model_name = $Model->name;

          $this->Behaviors->attach('Containable');
          return $this->find('all', array(
            'fields' => array(
              '*',
              '( 3959 * acos( cos( radians('.$options['latitude'].') ) * cos( radians( '.$model_name.'.latitude ) ) * cos( radians( '.$model_name.'.longitude ) - radians('.$options['longitude'].') ) + sin( radians('.$options['latitude'].') ) * sin( radians( '.$model_name.'.latitude ) ) ) ) AS distance' 
            ),
            'contain' => array(
              'User','UserInfo', 'GigPostAndCategory.Category','Location','GigPostAndCalender'

            ),
            'conditions' => array(
              'GigPost.active' => 1

            //'OrderGigPost.request' => 0
            ),
            'order' => 'GigPost.gig_post_id DESC',

            'recursive' => 0
          ));
        }

你可以这样调用它:

$query = $this->Model->getAllGigPosts(array('latitude' => XX.XXXXXX,'longitude' => -YY.YYYYYYYYY    ));

或者简单地

$query = $this->Model->getAllGigPosts();