mercredi 16 mai 2018

Laravel: Querybuilder-Select including float does not work

I have this scope function in my User class:

  public static function scopeGetByDistance($query,$lat, $lng, $max_distance)
    {
      return $query->select('*','( 3959 * cos( radians( '.$lat.') )  - radians(' . $lng . ') ) + sin( radians(lat) )) AS distance')
                   ->having('distance', '<', $max_distance )
                   ->orderBy( 'distance', 'ASC' );
   }

If I do

 \App\User::getByDistance('52.495492', '13.3436762', 30)->get();

I get an error, because select will convert 52.495492 to 52'.'495492

This is the full error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.495492) ) * sin( radians(lat) ) ) ) as distance from users where (type ' at line 1 (SQL: select *, ( 3959 * acos( cos( radians( 52.495492) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(13.3436762) ) + sin( radians(52.495492) ) * sin( radians(lat) ) ) ) as distance from users where (type = dvp or type = ecp) and (lat is not null and lng is not null) having distance < 150 order by distance asc)

If use selectRaw instead of select:

return $query->selectRaw('*, ( 3959 * cos( radians( '.$lat.') )  - radians(' . $lng . ') ) + sin( radians(lat) )) AS distance')
        ->having('distance', '<', $max_distance )
        ->orderBy( 'distance', 'ASC' );

then the whole selectRaw part seems to be missing. I get this error then:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from users where (type = dvp or type = ecp) and (lat is not null and lng is not null) having distance < 150)

How can I use select on the query builder so that it can contain floats?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire