How to use raw SELECT and WHERE on aliased column with Laravel?

Question

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

man it is really unclear, do you really thing that you want your code look like this? Use relations laravel.com/docs/5.6/eloquent-relationships — Adam Kozlowski
@AdamKozlowski it's a query on just one table. No place for relationships. — she hates me
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.
@N.B. havingRaw() also didn't work with functions. It's a simple conditional query based on given filters. — she hates me
"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.

Jonas Staudenmeir Jonas Staudenmeir · Accepted Answer · 2018-04-11T15:02:37

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

How to use raw SELECT and WHERE on aliased column with Laravel?

1 Answers