Laravel子查询whereIn

问题描述：

我遇到查询问题。我想做一个子查询，但它不返回结果。Laravel子查询whereIn

这里是我的代码：

 $distance_representations = Representation::select('id') 
     ->where(DB::raw('round(6371 * acos(cos(radians(45.0)) * cos(radians(latitude)) * cos(radians(longitude) - radians(2.7)) + sin(radians(45.0)) * sin(radians(latitude)))::numeric, 1) < 50')) 
     ->limit(10) 
     ->get(); 

    $representations = Representation::select('id', 'city', DB::raw('round(6371 * acos(cos(radians(45.0)) * cos(radians(latitude)) * cos(radians(longitude) - radians(2.7)) + sin(radians(45.0)) * sin(radians(latitude)))::numeric, 1) < 50')) 
     ->whereIn('id', $distance_representations) 
     ->get(); 

    return view('search', array('representations' => $representations));

的PostgreSQL相当于：

select id, city, round((6371 * acos(cos(radians(45.0)) * cos(radians(latitude)) * cos(radians(longitude) - radians(2.7)) + sin(radians(45.0)) * sin(radians(latitude))))::numeric, 1) as distance 
from representations 
where id in (
    select id 
    from representations 
    where round((6371 * acos(cos(radians(45.0)) * cos(radians(latitude)) * cos(radians(longitude) - radians(2.7)) + sin(radians(45.0)) * sin(radians(latitude))))::numeric, 1) < 50 
    limit 10 
)

谢谢

你在你的主要select子句中有一个比较 – inarilo

答

也许你可以试试这个

$distance_representations = Representation::find('id') 
    ->where(DB::raw('round(6371 * acos(cos(radians(45.0)) * cos(radians(latitude)) * cos(radians(longitude) - radians(2.7)) + sin(radians(45.0)) * sin(radians(latitude)))::numeric, 1) < 50')) 
    ->limit(10) 
    ->get(); 

    $representations = Representation::all() 
    ->where(DB::raw('round(6371 * acos(cos(radians(45.0)) * cos(radians(latitude)) * cos(radians(longitude) - radians(2.7)) + sin(radians(45.0)) * sin(radians(latitude)))::numeric, 1) < 50')) 
    ->whereIn('id', $distance_representations.id) 
    ->get();

希望这会帮助你。

答

我不明白你为什么需要子查询？除非我错过了某些东西，否则您应该可以使用单个查询来选择您的数据。

$representations = Representation::selectRaw('id, city, round(6371 * acos(cos(radians(45.0)) * cos(radians(latitude)) * cos(radians(longitude) - radians(2.7)) + sin(radians(45.0)) * sin(radians(latitude)))::numeric, 1) < 50 AS distance') 
    ->where('distance', '<', 50) 
    ->limit(10) 
    ->get();

谢谢你，但是你不能在where子句中使用别名 – jeyGey

答

我找到了解决方案。我无法做得更好。谢谢。

此解决方案正常工作！

$representations_with_distance = Representation::select('id', 'city', DB::raw('round(6371 * acos(cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?)) + sin(radians(?)) * sin(radians(latitude)))::numeric, 1) as distance')); 

$representation = DB::table(DB::raw("({$representations_with_distance->toSql()}) as rd")) 
    ->where('distance', '<', '?') 
    ->orderBy('distance') 
    ->offset(0) 
    ->limit(10) 
    ->setBindings([$latitude, $longitude, $latitude, $radius]) 
    ->get(); 

return view('search', array('representations' => $representation));

Laravel子查询whereIn

相关推荐