How to write raw query in Laravel?-CodePudding

There is a query:

SELECT ST_DistanceSpheroid(geometry(location), ST_GeomFromText('POINT(37.854289 55.685333)'), 'SPHEROID["WGS 84",6378137,298.257223563]')
        FROM users

How to pass parameter 37.854289 55.685333?

Also I tried this:

$point = "37.854289 55.685333";
      return DB::table('users')
            ->select(DB::raw('ST_DistanceSpheroid(geometry(location), ST_GeomFromText(\'POINT(?)\'), \'SPHEROID["WGS 84",6378137,298.257223563]\''), [$point])
            ->get();

I got this error:

 "message": "stripos(): Argument #1 ($haystack) must be of type string, array given",

My attempt bases accepted question:

$lon = 37.857397;
$lat = 55.685333;

return DB::table('users')
    ->selectRaw(
        "(ST_DistanceSpheroid(
        geometry(location),
        ST_GeomFromText('POINT(? ?)'),
        'SPHEROID[?, ?, ?]'
    )) as distance",
        [$lon, $lat, 'WGS 84', 6378137, 298.257223563]
    )->leftJoin('doctors', 'doctors.user_id', 'users.id')->orderBy('distance', 'ASC')->get();

I have got an error:

{
    "message": "PDO: SQLSTATE[XX000]: Internal error: 7 ОШИБКА:  SPHEROID parser - couldnt parse the spheroid\nLINE 4:             'SPHEROID[?, ?, ?]'\n                    ^ (SQL: select (ST_DistanceSpheroid(\n            geometry(location),\n            ST_GeomFromText('POINT(37.857397 55.685333)'),\n            'SPHEROID[WGS 84, 6378137, 298.257223563]'\n        )) as distance from \"users\" left join \"doctors\" on \"doctors\".\"user_id\" = \"users\".\"id\" order by \"distance\" asc)"
}

CodePudding user response：

You almost got it. The [$point] parameter should be the second parameter of DB::raw($query, $bindings) but you added it as a second parameter to select().

// What you have
->select(DB::raw(...), [$point])
// correct syntax
->select(DB::raw(..., [$point]))

If you've got nothing else to put in your select clause, might as well use selectRaw(). It's the same as select(DB::raw()).

DB::table('users')
    ->selectRaw('ST_DistanceSpheroid(geometry(location), ST_GeomFromText(\'POINT(?)\'), \'SPHEROID["WGS 84",6378137,298.257223563]\')', [$point])

Personally, I'd write the query like this:

$query = DB::table('users')
    ->selectRaw(
        "ST_DistanceSpheroid(
            geometry(location),
            ST_GeomFromText('POINT(? ?)'),
            'SPHEROID[?, ?, ?]'
        )",
        [37.854289, 55.685333, 'WGS 84', 6378137, 298.257223563]
    )
    ->get();