0

I'm trying to use raw select with Laravel Eloquent. But unfortunately I cannot use WHERE condition for aliased "SELECT AS" field (distance).

I get "Column not found: 1054 Unknown column 'distance' in 'where clause'" error.

How can I use "distance" as a condition with Eloquent?

Here is my code

            $firebaseUsers = FirebaseUser::when( (!empty($distance)) , function ($query) use ($distance, $user) {
                return $query->select('firebase_users.*', DB::raw("ST_Distance_Sphere( POINT(".$user->latitude.", ".$user->longitude."), POINT(latitude, longitude) ) as distance") )
                    ->whereNotNull('latitude')
                    ->whereNotNull('longitude')
                    ->whereRaw('distance <= ?',  [$distance * 1000]);
            })
            ->where('firebase_id', '!=', $user->firebase_id)
            ->orderByRaw( "FIELD(paid_status, 'yes', 'no')" )
            ->orderBy('last_online', 'DESC')
            ->paginate(30);
she hates me
  • 1,212
  • 5
  • 25
  • 44
  • 1
    man it is really unclear, do you really thing that you want your code look like this? Use relations https://laravel.com/docs/5.6/eloquent-relationships – Adam Kozlowski Apr 11 '18 at 14:55
  • @AdamKozlowski it's a query on just one table. No place for relationships. – she hates me Apr 11 '18 at 14:59
  • 1
    What you're after is `->havingRaw('distance <= ?', [$distance * 1000]);` instead of `whereRaw`. Personally, I'd create a view and then have Eloquent model deal with the view instead of this cabbage.. – N.B. Apr 11 '18 at 15:01
  • @N.B. havingRaw() also didn't work with functions. It's a simple conditional query based on given filters. – she hates me Apr 11 '18 at 15:04
  • `"Didn't work"` - absolutely does not help. *HOW* did it not work? If `havingRaw` doesn't exist, try `having`. For derived columns, you can't use `where`, you need to use `having`. – N.B. Apr 11 '18 at 15:06
  • @N.B. I'm getting same SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' error. – she hates me Apr 11 '18 at 15:09

1 Answers1

4

You need to use having() for derived columns:

->having('distance', '<=', $distance * 1000);

But pagination doesn't work with having(): https://github.com/laravel/framework/issues/3105

Jonas Staudenmeir
  • 24,815
  • 6
  • 63
  • 109
  • also tried this both in first when() scope and out of the scope. still getting same error. – she hates me Apr 11 '18 at 15:06
  • You still get `Unknown column 'distance' in 'where clause'"`? – Jonas Staudenmeir Apr 11 '18 at 15:21
  • simply "SELECT ST_Distance_Sphere(POINT(29.055334, 41.002261), gps_position) AS distance FROM firebase_users HAVING distance > 500;" works fine when I connect to mysql and execute directly. But getting " Column not found: 1054 Unknown column 'distance' in 'having clause" error with laravel. Also when I print results without using where/having, it prints distance correctly. So distance is there but having/where cannot use it! – she hates me Apr 11 '18 at 15:25
  • Unfortunately get() is also doesn't work with it. Not just paginate(). – she hates me Apr 11 '18 at 15:47
  • Sorry, it was my fault, you're right, get() works fine but paginate() doesnt. Thank you very much. Do you have any idea for alternate ways? – she hates me Apr 11 '18 at 16:04
  • There's a link in the issue: https://stackoverflow.com/questions/19349397/how-to-use-paginate-with-a-having-clause-when-column-does-not-exist-in-table/20945960#20945960 – Jonas Staudenmeir Apr 11 '18 at 16:04
  • As a note for others, simplePaginate() not affected by this bug. – she hates me Apr 11 '18 at 16:20