php 如何将参数绑定到 Laravel 中用于模型的原始数据库查询？

How to bind parameters to a raw DB query in Laravel that's used on a model?

提问by MarkL

Re,

关于，

I have the following query:

我有以下查询：

$property = 
    Property::select(
        DB::raw("title, lat, lng, ( 
            3959 * acos( 
                cos( radians(:lat) ) * 
                cos( radians( lat ) ) * 
                cos( radians( lng ) - radians(:lng) ) + 
                sin( radians(:lat) ) * 
                sin( radians( lat ) ) 
            ) 
        ) AS distance", ["lat" => $lat, "lng" => $lng, "lat" => $lat])
    )
    ->having("distance", "<", $radius)
    ->orderBy("distance")
    ->take(20)
    ->get();

It doesn't work: Invalid parameter number: mixed named and positional parameters.

它不起作用：Invalid parameter number: mixed named and positional parameters。

Does anyone know a trick or a workaround (I can obviously write the full query but prefer to use fluent builder).

有谁知道技巧或解决方法（我显然可以编写完整的查询，但更喜欢使用流畅的构建器）。

回答by MarkL

OK, after some experimenting, here's the solution that I came up with:

好的，经过一些实验，这是我想出的解决方案：

$property = 
    Property::select(
        DB::raw("title, lat, lng, ( 
            3959 * acos( 
                cos( radians(  ?  ) ) *
                cos( radians( lat ) ) * 
                cos( radians( lng ) - radians(?) ) + 
                sin( radians(  ?  ) ) *
                sin( radians( lat ) ) 
            )
       ) AS distance")
    )
    ->having("distance", "<", "?")
    ->orderBy("distance")
    ->take(20)
    ->setBindings([$lat, $lng, $lat,  $radius])
    ->get();

Basically, setBindingshas to be called on the query. Wish this was documented!

基本上，setBindings必须在查询时调用。希望这被记录在案！

回答by bluesky777

Old question, but if we have to repeat a variable, we have to change its key value in the bindings array.

老问题，但如果我们必须重复一个变量，我们必须更改它在 bindings 数组中的键值。

    $property = Property::select(
        DB::raw("title, lat, lng, ( 3959 * acos( cos( radians(:lat) ) * 
        cos( radians( lat ) ) * cos( radians( lng ) - radians(:lng) ) + 
        sin(radians(:lat_i) ) * sin( radians( lat ) ) ) ) AS distance"),
        ["lat" => $lat, "lng" => $lng, "lat_i" => $lat]);

That's enough.

就够了。

回答by Stefan Z

why not?

为什么不？

    $latitude = $request->input('latitude', '44.4562319000');
    $longitude = $request->input('longitude', '26.1003480000');
    $radius = 1000000;

    $locations = Locations::selectRaw("id, name, address, latitude, longitude, image_path, rating, city_id, created_at, active,
                         ( 6371 * acos( cos( radians(?) ) *
                           cos( radians( latitude ) )
                           * cos( radians( longitude ) - radians(?)
                           ) + sin( radians(?) ) *
                           sin( radians( latitude ) ) )
                         ) AS distance", [$latitude, $longitude, $latitude])
        ->where('active', '1')
        ->having("distance", "<", $radius)
        ->orderBy("distance")
        ->get();

回答by jovani

I encountered same issue just recently and the answer is in that error message mixed named and positional parameters. In your case, the :latand :lngare named parameters while you have $radiusas positional. So one possible fix to your issue is make use of havingRaw()and apply named parameters.

我最近遇到了同样的问题，答案就在该错误消息中mixed named and positional parameters。在您的情况下，:lat和:lng是命名参数，而您具有$radius作为位置。因此，解决您的问题的一种可能方法是使用havingRaw()并应用命名参数。

--havingRaw('distance < :radius', ['radius' => $radius])

--havingRaw('distance < :radius', ['radius' => $radius])

回答by malhal

I ported the nearby search from Doctrine v1 to Laravel, check it out here.

我将附近的搜索从 Doctrine v1 移植到 Laravel，请在此处查看。

Just add the Geographicaltrait to the model then you can do:

只需将Geographical特征添加到模型中，然后您就可以执行以下操作：

$model->newDistanceQuery($request->query('lat'), $request->query('lon'))->orderBy('miles', 'asc')->get();

It works by using selectRaw with bindings like this:

它通过将 selectRaw 与如下绑定一起使用来工作：

$sql = "((ACOS(SIN(? * PI() / 180) * SIN(" . $latName . " * PI() / 180) + COS(? * PI() / 180) * COS(" . $latName . " * PI() / 180) * COS((? - " . $lonName . ") * PI() / 180)) * 180 / PI()) * 60 * ?) as " . $unit;
if($kilometers){
    $query->selectRaw($sql, [$lat, $lat, $lon, 1.1515 * 1.609344]);
}
else{
    // miles
    $query->selectRaw($sql, [$lat, $lat, $lon, 1.1515]);
}

回答by Danilo Lima

$select = <<<SQL
    title,
    lat,
    lng,
    (3959*acos(cos(radians( ? ))*cos(radians(lat))*cos(radians(lng)-radians( ? ))+sin(radians( ? ))*sin(radians(lat)))) AS distance
SQL;

$property = Property::selectRaw($select, [$lat, $lng, $lat])
    ->having('distance', '<', $radius)
    ->orderBy('distance')
    ->take(20)->get();