Patrioticcow Patrioticcow - 3 years ago 117
MySQL Question

How to return an array using Laraveel Eloquent select?

I have this class

<?php

namespace App\Models\Tables;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;

class ZipCode extends Model
{
protected $table = 'zip';
public $timestamps = false;
protected $fillable = [
'latitude',
'longitude',
];

public static function radiusSearch($lat, $lng)
{
$circle_radius = 3959;
$subQuery = '(SELECT *, (' . $circle_radius . ' * acos(cos(radians(' . $lat . ')) * cos(radians(latitude)) * cos(radians(longitude) - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians(latitude)))) AS distance FROM zip) AS distances';

$result = ZipCode::select('*')
->from(ZipCode::raw($subQuery))
->limit(20);

var_dump($result);

return $result;
}
}


when I call
radiusSearch(...)
I get a
Illuminate\Database\Eloquent\Builder
object and not the actual result

$result->get()
doesn't work, I get
Object of class Illuminate\Database\Eloquent\Builder could not be converted to string


This generic query works just fine
ZipCode::where('zip', '11111')->get()
just not the above one

Any ideas?

EDIT: the
$result
returns something like this:

object(Illuminate\Database\Eloquent\Builder)[170]
protected 'query' =>
object(Illuminate\Database\Query\Builder)[164]
public 'connection' =>
object(Illuminate\Database\MySqlConnection)[166]
protected 'pdo' =>
object(PDO)[175]
...
protected 'model' =>
object(App\Models\Tables\ZipCode)[171]
protected 'table' => string 'zip' (length=9)
public 'timestamps' => boolean false
...


If I do
$result->getModel()->get()->toArray()
this returns all resutls from the table

Answer Source

Here is the solution I came up with based on some other community answers

public static function getClosestCities($lat, $lng, $city, $distance = 20, $limit = 20)
{
    $coordinates = ['latitude' => $lat, 'longitude' => $lng];
    $cares       = self::scopeIsWithinMaxDistance(ZipCode::raw('select *'), $coordinates, $distance, $city)->limit($limit)->get();

    return $cares ? $cares->toArray() : null;
}


public static function scopeIsWithinMaxDistance($query, $coordinates, $radius = 5, $city)
{
    $haversine = "(3961 * acos(cos(radians(" . $coordinates['latitude'] . ")) 
                * cos(radians(`latitude`)) 
                * cos(radians(`longitude`) 
                - radians(" . $coordinates['longitude'] . ")) 
                + sin(radians(" . $coordinates['latitude'] . ")) 
                * sin(radians(`latitude`))))";

    return $query->select('*')
        ->selectRaw("{$haversine} AS distance")
        ->orderByRaw("{$haversine} asc")
        ->whereRaw("{$haversine} < ?", [$radius])
        ->groupBy("city");
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download