php - 将此复杂查询转换为 ORM 或 Laravel 查询生成器

Question

我一整天都在尝试将此查询转换为与 Laravel 兼容的查询。我找不到有关在查询中使用 AS 的任何信息。任何帮助将不胜感激。

SELECT zipcode, city, state, lat, lng, distance_in_mi
    FROM (
    SELECT zipcode, city, state, lat, lng, r, ( 3963.17 * ACOS( COS( RADIANS( latpoint ) ) * COS(        RADIANS( lat ) ) * COS( RADIANS( longpoint ) - RADIANS( lng ) ) + SIN( RADIANS( latpoint ) ) * SIN( RADIANS( lat ) ) ) ) AS distance_in_mi
    FROM zipcode
    JOIN (
    SELECT $current_lat AS latpoint, $current_lng AS longpoint, 10 AS r
    ) AS p
    WHERE lat
    BETWEEN latpoint - ( r /69 ) 
    AND latpoint + ( r /69 ) 
    AND lng
    BETWEEN longpoint - ( r / ( 69 * COS( RADIANS( latpoint ) ) ) ) 
    AND longpoint + ( r / ( 69 * COS( RADIANS( latpoint ) ) ) )
    ) d
    WHERE distance_in_mi <= r
    ORDER BY distance_in_mi

这是我到目前为止所拥有的:

$data_object = DB::table('zipcode', function($query)
            {
        $query->select('zipcode, city, state, lat, lng, r, ( 3963.17 * ACOS( COS( RADIANS( latpoint ) ) * COS( RADIANS( lat ) ) * COS( RADIANS( longpoint ) - RADIANS( lng ) ) + SIN( RADIANS( latpoint ) ) * SIN( RADIANS( lat ) ) ) ) AS distance_in_mi')
          ->from('zipcode')
          ->join('zipcode', function($query1)
            {
            $query1->select("($current_lat AS latpoint, $current_lng AS longpoint, 10 AS r) AS p")
                    ->whereBetween('lat', 'latpoint - ( r /69 )' )
                    ->whereBetween('lng', 'longpoint - ( r / ( 69 * COS( RADIANS( latpoint ) ) ) ) AND longpoint + ( r / ( 69 * COS( RADIANS( latpoint ) ) ) )' );

            });
            })
            ->where('distance_in_mi', '<=', 'r')
                    ->orderBy('distance_in_mi')
                    ->get();

Answer 1

基于this question

并回答:

Laravel 支持使用 AS 对表和列使用别名。尝试一下

$users = DB::table('really_long_table_name AS t')
           ->select('t.id AS uid')
           ->get();

基于此，您可以创建查询，并将 AS 放入 select 或表中。