Perform query on a calculated column using Laravel

Question

I want to perform the following SQL statement in PHP using Laravel's Eloquent model:

SELECT *, (3959 * acos(cos(radians(37)) * cos(radians(lat)) * cos(radians(lng) - radians(-122)) + sin(radians(37)) * sin(radians(lat )))) AS distance
FROM example_retailers
HAVING distance < 1000
ORDER BY distance
OFFSET 4
LIMIT 3;

I am struggling to translate this into a Laravel's Eloquent Model (mainly the calculated column).

This is what I have so far:

ExampleRetailer::
    // TODO: add calculated row
    where('distance', '<', 100)
    ->orderBy('distance')
    ->skip(4)
    ->limit(3)
    ->get();

Possible duplicate of [Haversine distance calculation between two points in Laravel](http://stackoverflow.com/questions/37876166/haversine-distance-calculation-between-two-points-in-laravel) — Clive, Jan 29 '17 at 12:54
@Clive That question does not tell me how to do it using the eloquent class — Yahya Uddin, Jan 29 '17 at 14:32

score 2 · Accepted Answer · edited Jan 29 '17 at 14:14

2

You can use raw queries (source )

DB::table('example_retailers')
->select(DB::raw('*, (3959 * acos(cos(radians(37)) * cos(radians(lat)) * cos(radians(lng) - radians(-122)) + sin(radians(37)) * sin(radians(lat )))) AS distance'))
->having('distance', '<', 100)
->orderBy('distance')
->skip(4)
->limit(3)
->get();

edited Jan 29 '17 at 14:14

Yahya Uddin

26,997
35
140
231

answered Jan 29 '17 at 13:00

Erkan Özkök

895
12
25

I get an error when running this: `unknown column 'distance'` – Yahya Uddin Jan 29 '17 at 13:56
1

You made a small mistake. It should be `having`, not `where`. – Yahya Uddin Jan 29 '17 at 14:14
How would I do it using the eloquent class? I updated the question to make this more clear. – Yahya Uddin Jan 29 '17 at 14:32

score -1 · Answer 2 · edited Dec 20 '20 at 00:30

-1

If it's only to make a new row using SQL:

SELECT *, CASE WHEN (3959 * acos(cos(radians(37))
                          * cos(radians(lat))
                          * cos(radians(lng) - radians(-122))
                          + sin(radians(37))
                          * sin(radians(lat )))) < 100 THEN END AS NewRow
FROM example_retailers

edited Dec 20 '20 at 00:30

Tony

9,672
3
47
75

answered Jan 29 '17 at 12:58

Raphael Rohner

21
5

Thats not even in Laravel – Yahya Uddin Jan 29 '17 at 13:54

Perform query on a calculated column using Laravel

2 Answers2