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
.
它不起作用:参数号无效:混合命名和位置参数。
Does anyone know a trick or a workaround (I can obviously write the full query but prefer to use fluent builder).
有没有人知道一个技巧或解决方法(我显然可以编写完整的查询,但更喜欢使用流利的构建器)。
6 个解决方案
#1
74
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, setBindings
has to be called on the query. Wish this was documented!
基本上,必须在查询上调用setBindings。希望这是有记录的!
#2
18
Old question, but if we have to repeat a variable, we have to change its key value in the bindings array.
老问题,但如果我们必须重复一个变量,我们必须在绑定数组中更改其键值。
$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.
这就够了。
#3
8
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();
#4
2
I encountered same issue just recently and the answer is in that error message mixed named and positional parameters
. In your case, the :lat
and :lng
are named parameters while you have $radius
as positional. So one possible fix to your issue is make use of havingRaw()
and apply named parameters.
我最近遇到了同样的问题,答案是在该错误消息中混合了命名和位置参数。在你的情况下,:lat和:lng是命名参数,而你有$ radius作为位置。因此,对您的问题进行一种可能的解决方法是使用havingRaw()并应用命名参数。
--havingRaw('distance < :radius', ['radius' => $radius])
--havingRaw('distance <:radius',['radius'=> $ radius])
#5
0
$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();
#6
0
I ported the nearby search from Doctrine v1 to Laravel, check it out here.
我将附近的搜索从Doctrine v1移植到Laravel,在这里查看。
Just add the Geographical
trait to the model then you can do:
只需将地理特征添加到模型中即可:
$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]);
}
#1
74
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, setBindings
has to be called on the query. Wish this was documented!
基本上,必须在查询上调用setBindings。希望这是有记录的!
#2
18
Old question, but if we have to repeat a variable, we have to change its key value in the bindings array.
老问题,但如果我们必须重复一个变量,我们必须在绑定数组中更改其键值。
$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.
这就够了。
#3
8
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();
#4
2
I encountered same issue just recently and the answer is in that error message mixed named and positional parameters
. In your case, the :lat
and :lng
are named parameters while you have $radius
as positional. So one possible fix to your issue is make use of havingRaw()
and apply named parameters.
我最近遇到了同样的问题,答案是在该错误消息中混合了命名和位置参数。在你的情况下,:lat和:lng是命名参数,而你有$ radius作为位置。因此,对您的问题进行一种可能的解决方法是使用havingRaw()并应用命名参数。
--havingRaw('distance < :radius', ['radius' => $radius])
--havingRaw('distance <:radius',['radius'=> $ radius])
#5
0
$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();
#6
0
I ported the nearby search from Doctrine v1 to Laravel, check it out here.
我将附近的搜索从Doctrine v1移植到Laravel,在这里查看。
Just add the Geographical
trait to the model then you can do:
只需将地理特征添加到模型中即可:
$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]);
}