如何将哈维逊公式放入laravel 4查询中

Question

我有以下代码。我想对距离进行排序。作业表中的距离列是lat，我将从GET参数中获得两个变量，假设$lat，$lng。我想对最小距离进行排序。在mysql中获得最低距离的代码是这样的。latpoint和longpoint将来自$_GET

SELECT lat, lng,
  111.045* DEGREES(ACOS(COS(RADIANS(latpoint))
             * COS(RADIANS(latitude))
             * COS(RADIANS(longpoint) - RADIANS(longitude))
             + SIN(RADIANS(latpoint))
             * SIN(RADIANS(latitude)))) AS distance_in_km

从工作按距离in_km排序限制15

Job::with(array("job_categories" => function($query) {
        $query -> with('cat_detail');
    })) -> whereHas('job_categories', function($query) use ($cats_filter_value) {
        //$query->where('category_id',13);
        if (!empty($cats_filter_value)) {
            $query -> whereIn('category_id', $cats_filter_value);
        }
    }) -> with(array("job_skills" => function($query) {
        $query -> with('skill_detail');
    })) -> whereHas('job_skills', function($query) use ($skill_filter_value) {
        //$query->where('category_id',13);
        if (!empty($skill_filter_value)) {
            $query -> whereIn('skills_id', $skill_filter_value);
        }
    }) -> with("job_bids") -> with('job_follow') -> with("job_watch") -> with(array("job_award_me" => function($query) use ($conditions) {
        $query -> with('job_award_detail');
    })) -> with("job_to_user") -> orderBy($a, $c) -> where(function($query) use ($conditions) {
        if (isset($conditions['user_id']) || isset($conditions['user_to_id'])) {
            //$query->orWhere('job_status','=',$conditions['user_id']);
        } else {
            $query -> where('job_status', $conditions['job_status']);
        }
        if (isset($conditions["job_awarded"])) {
            if (isset($conditions['user_id']) || isset($conditions['user_to_id'])) {
            } else {
                $query -> where('job_awarded', $conditions['job_awarded']);
            }
        }
        if (isset($conditions["budget"])) {
            $query -> where('budget', ">", $conditions['budget']['start']);
            $query -> where('budget', "<=", $conditions['budget']['end']);
        }
        if (isset($conditions['job_filter_value'])) {
            //
            foreach ($conditions['job_filter_value'] as $key => $value) {
                $query -> where('job_type', 'LIKE', '%' . $value . '%');
            }
        }
        if (isset($conditions['rating'])) {
            $query -> where('rating', '<=', $conditions['rating']);
        }
        if (isset($conditions['job_title'])) {
            $query -> where('job_title', 'LIKE', '%' . $conditions['job_title'] . '%');
        }
        if (isset($conditions['user_id'])) {
            $query -> where('user_id', '=', $conditions['user_id']);
        }
        if (isset($conditions['main_type'])) {
            $query -> where('is_shoot', '=', $conditions['main_type']);
            $query -> whereIn('job_status', array(2, 3)) -> whereIn('job_awarded', array(0, 1));
        }
        if (isset($conditions['token'])) {
            //foreach ($conditions['token'] as $key => $value) {
            //$c = implode(',', $conditions['token']);
            $query -> whereIn('job_token', $conditions['token']);
            //}
        }
        if (isset($conditions['location'])) {
            //foreach ($conditions['token'] as $key => $value) {
            //$c = implode(',', $conditions['token']);
            $query -> whereIn('user_id', $conditions['location']);
            //}
        }
        if (isset($conditions['job_to_ids'])) {
            $query -> whereIn('id', $conditions['job_to_ids']);
            //$query -> orWhere('id', '=', $value);
        }
    }) -> paginate($records);

我必须在laravel查询中按距离排序。由于

Answer 1

您可以尝试orderBy()作为:

->orderBy(DB::raw('111.045* DEGREES(ACOS(COS(RADIANS('.$latpoint.')) * COS(RADIANS(lat)) * COS(RADIANS('.$longpoint.') - RADIANS(lng)) + SIN(RADIANS('.$latpoint.')) * SIN(RADIANS(lat))))'));