CODEWITHSUNDEEP
laravelsubquery
jeyGey
jeyGey

Reputation: 191

Laravel subquery whereIn

I have a problem with a query. I want to make a subquery but it does not return results.

Here is my code :

        $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 equivalent :

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
)

Thank you

Upvotes: 1

Views: 560

Answers (3)

jeyGey
jeyGey

Reputation: 191

I found the solution. I can not do better. Thank you.

This solution WORKS !

$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));

Upvotes: 1

fubar
fubar

Reputation: 17378

I don't see why you need the sub-query? Unless I'm missing something, you should be able to select your data using a single query.

$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();

Upvotes: 1

Almaida Jody
Almaida Jody

Reputation: 596

perhaps you can try this

    $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();

hope this will help you.

Upvotes: 1

Related Questions