Laravel / Eloquent - 无法执行原始查询

Question

This is the function that is executing the query:

    public static function getNearbyCityReport(float $lon, float $lat, float $rangeInKm){
    $query = "
        SELECT
            name,
            population,
            round((ST_Distance(rpoint, 'POINT( ? ? )', True)/1000)::numeric, 1) as distance,
            round(degrees(ST_Azimuth(rpoint, 'POINT( ? ? )'))::numeric,1) AS azimuth
        FROM
            gis_cities
        WHERE
            ST_DWithin(rpoint, 'POINT( ? ? )', 1000*?, True)
            AND
            feature_code != 'PPLX'
        ORDER BY distance;        
    ";
    $query = preg_replace('#
#', ' ', $query);
    $query = trim(preg_replace('#\s{2,}#', ' ', $query));
    $expression = DB::raw($query);
    $result = DB::select($expression, [$lon, $lat, $lon, $lat, $lon, $lat, $rangeInKm]);
    dd($result);

When running this function I get the following exception:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined (SQL: SELECT name, population, round((ST_Distance(rpoint, 'POINT( 24.8 43.3648 )', True)/1000)::numeric, 1) as distance, round(degrees(ST_Azimuth(rpoint, 'POINT( 24.8 43.3648 )'))::numeric,1) AS azimuth FROM gis_cities WHERE ST_DWithin(rpoint, 'POINT( 24.8 43.3648 )', 1000*300, True) AND feature_code != 'PPLX' ORDER BY distance;)

The weird thing is that nothing seems to be missing from the built query shown in the exception and if I run that query on my database it works properly. How can I make this work?

LATER EDIT:

Apparently the problem actually lies at PDO level and is caused by the fact that I am trying to add some parameters inside a quoted string:

(ST_Distance(rpoint, 'POINT( ? ? )', True)/1000)

See the 'POINT( ? ? )' parts. If I remove all the quotes from my query then it works but of course it is not a valid postgis query anymore. Anybody know how this query should be written so that PDO will accept it ?

Answer 1

You have to merge the two coordinates into a single binding:

(ST_Distance(rpoint, 'POINT( ? )', True)/1000)

$result = DB::select($expression, [$lon.' '.$lat, ...]);