Converting SQL Query into Eloqeunt

Searching I think I need to use "whereRaw"? Any help on that?

First you need to go to your config/database.php and set strict to false. Then try to use this, it's something like that:

\App\User::selectRaw('id, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin( radians( latitude ) ) ) ) distance')->havingRaw('distance < 25')->orderBy('distance')->take(20)->get();

I will try this when I get home. I am away right now. Will let you know how it goes.

I would use your original query with getPdo ().

@Loach ok.

I did that based on the serie Laravel From Scratch EP 20.

https://laracasts.com/series/laravel-from-scratch-2017/episodes/20

If it return errors, tell me! :)

I added 'AS distance' to that and am getting an error.

Non-grouping field 'distance' is used in HAVING clause (SQL: select ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance from `users` where (`role_id` = 2 and `ban_user` = 0) having distance < 25)

Actually i forgot to set strict to "false" as you mentioned. I am not getting any errors with that query, but now I have to tie it into my actual code to see if it is working.

Does not seem to get the results with my code. I am not sure I am passing variables correctly. Does anything look off?

  $theCity = City::where('region_id',$request["region_id"])->where('name',$request['city_id'] )->first();
            $theLat = $theCity->latitude;
            $theLong = $theCity->longitude;
            $theRadius = $request['radius'];

             $user->selectRaw('( 3959 * acos( cos( radians("{$theLat}") ) * cos( radians( lat ) ) * cos( radians(lng ) - radians("{$theLong}") ) + sin( radians("{$theLat}") ) * sin( radians( lat ) ) ) ) AS distance')
               ->havingRaw('distance <= "{$theRadius}"');

            return view('frontend.search_results')->with(['results'=>$user->get()]);

Yep I am passing the variables wrong. DD the query yields the following.

"select ( 3959 * acos( cos( radians("{$theLat}") ) * cos( radians( lat ) ) * cos( radians(lng ) - radians("{$theLong}") ) + sin( radians("{$theLat}") ) * sin( radians( lat ) ) ) ) AS distance from `users` where (`role_id` = ? and `ban_user` = ?) having distance <= "{$theRadius}"

OK here is the final code. But I am getting an error. "Trying to get property of non-object". If I remove this code my query works as usual.

   $theCity = City::where('region_id',$request["region_id"])->where('name',$request['city_id'] )->first();
            $theLat = $theCity->latitude;
            $theLong = $theCity->longitude;
            $theRadius = $request['radius'];

        $user->selectRaw("( 3959 * acos( cos( radians({$theLat}) ) * cos( radians( lat ) ) * cos( radians(lng ) - radians({$theLong}) ) + sin( radians({$theLat}) ) * sin( radians( lat ) ) ) ) AS distance")
               ->havingRaw("distance <= {$theRadius}");

Wonder if it could be because of 'role_id' = ? and 'ban_user' = ?. Those should be 2 and 0 respectively.

What does $user return?

How do you mean? dd($user) ? I am just calling $user-get()

Here is my complete code. I am unsure about the User::query() thing never seen it before, but another coder added that.

  public function search(Request $request){


      $user = User::query();
      $matchThese=['role_id'=>2,'ban_user'=>0];
      $user->where($matchThese);


        if(isset($request['city_id'])){
             $theCity = City::where('region_id',$request["region_id"])->where('name',$request['city_id'] )->first();
            $theLat = $theCity->latitude;
            $theLong = $theCity->longitude;
            $theRadius = $request['radius'];


          $user->selectRaw("( 3959 * acos( cos( radians({$theLat}) ) * cos( radians( lat ) ) * cos( radians(lng ) - radians({$theLong}) ) + sin( radians({$theLat}) ) * sin( radians( lat ) ) ) ) AS distance")
                ->havingRaw("distance <= {$theRadius}");
          }

            return view('frontend.search_results')->with(['results'=>$user->get()]);
   }

I'm doing some tests here.

OK thanks for the help.

What returns if you do dd($user->get()) inside and outside the if(isset($request['city_id'])). Just trying to check if this if is working and if the $user->get() is working .

Collection {#719 ▼
  #items: array:3 [▼
    0 => User {#714 ▶}
    1 => User {#713 ▶}
    2 => User {#712 ▶}
  ]
}

Must be an error elsewhere?

This is inside or outside the if?

Oh it is inside the if

Maybe the isset is not working, if outside the if doesn't show the same thing.

Try to change this if to if($request->has('city_id')) or if($request->filled('city_id')).

Documentation

https://laravel.com/docs/5.5/requests#retrieving-input

I removed most of the code from my search_results.blade.php and it is showing "your query returned 3 results". So I guess it is working. I just have to add the code back in and see what is causing the problem.

If it still causing problems post your view, so I can try to help you.

@Loach

Have you tried this?

DB::select(`id`)
        ->addSelect(DB::raw(`( 3959 * acos( cos( radians(37) ) * cos( radians(lat) ) * cos( radians(lng) - radians(-122) ) + sin( radians(37) ) * sin( radians(lat) ) ) )`  as distance))
        ->from(`markers`)
        ->having(`distance`, `<`, 25)
        ->orderByRaw(`distance ASC`)
        ->limit(20)
        ->get();

Although it is showing 3 users they have no attributes like "firstname", "lastname", etc. It is only showing 'distance' as an attribute which is strange. But it seems to be getting the correct number of users based on radius.

  #items: array:2 [▼
    0 => User {#719 ▼
      #fillable: array:3 [▼
        0 => "name"
        1 => "email"
        2 => "password"
      ]
      #hidden: array:2 [▼
        0 => "password"
        1 => "remember_token"
      ]
      #connection: "mysql"
      #table: null
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:1 [▼
        "distance" => 0.0
      ]
      #original: array:1 [▼
        "distance" => 0.0
      ]
      #casts: []
      #dates: []
      #dateFormat: null
      #appends: []
      #events: []
      #observables: []
      #relations: []
      #touches: []
      +timestamps: true
      #visible: []
      #guarded: array:1 [▶]
      #rememberTokenName: "remember_token"
    }

Outside the if it shows the correct attributes.

I added a "*" to the beginning of the query. I guess I left off that part it seems to add the attributes back, but I have to see if it works with the rest of the code. So I guess your first post was right.

$user->selectRaw("*,( 3959 * acos( cos( radians({$theLat}) ) * cos( radians( lat ) ) * cos( radians(lng ) - radians({$theLong}) ) + sin( radians({$theLat}) ) * sin( radians( lat ) ) ) ) AS distance")
                ->havingRaw("distance <= {$theRadius}");

It seems to work with all my other search parameters. Thanks so much for the help! I will accept your first post as the correct answer.

Actually i do have one more question. Those lat,lng,and radius are coming from a form. Since we are using raw queries do i need to be concerned with sql injection?

@Loach I don't know, I don't think so.