Convert this complex query to ORM or Laravel Query Builder

Question

I've been trying to convert this query to a laravel compatible query all day. I can't find any info on using AS in a query. Any help would be appreciated.

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

Here is what I have so far:

$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();

What have you tried so far? Take also a look at the documentation: http://laravel.com/docs/queries — Perry, Apr 15 '14 at 20:31
Yeah I have the docs open, now. I need to figure out how to handle AS in laravel's query builder. Cannot find it in the docs. — zeros-and-ones, Apr 15 '14 at 20:34

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

Based on this question

And answer :

Laravel supports aliases on tables and columns with AS. Try

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

Based on this, you can create your query, and simply put the AS in the select or tables.

edited May 23 '17 at 10:32

Community

1
1

answered Apr 15 '14 at 20:52

Paul Bele

1,514
1
11
12

Looks like what I was after. I'll post my query once I get it nailed down. I don't want to close this out until I get my query working. – zeros-and-ones Apr 15 '14 at 21:23

Convert this complex query to ORM or Laravel Query Builder

1 Answers1