将此复杂查询转换为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

基于这个问题,

答复:

Laravel支持在具有AS的表和列上使用别名。尝试

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

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