mercredi 11 avril 2018

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

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]);
            })
            ->when( (!empty($from_age) and !empty($to_age)) , function ($query) use ($to_age_timestamp, $from_age_timestamp) {
                return $query->whereBetween('birthdate', [$to_age_timestamp, $from_age_timestamp]);
            })
            ->when( (!empty($gender)) , function ($query) use ($gender) {
                return $query->where('gender', [$gender]);
            })
            ->when( (!empty($blockedUsers)) , function ($query) use ($blockedUsers) {
                return $query->whereNotIn('firebase_id', $blockedUsers);
            })
            ->when( (!empty($blockedByUsers)) , function ($query) use ($blockedByUsers) {
                return $query->whereNotIn('firebase_id', $blockedByUsers);
            })
            ->when( (!empty($with_photo_only)) , function ($query) {
                return $query->whereNotNull('face')->where('face', '!=', 'notset');
            })
            ->when( (!empty($name) and mb_strlen($name) > 2 ) , function ($query) use ($name) {
                return $query->where('name', 'like', '%'.$name.'%');
            })
            ->where('firebase_id', '!=', $user->firebase_id)
            ->orderByRaw( "FIELD(paid_status, 'yes', 'no')" )
            ->orderBy('last_online', 'DESC')
            ->paginate(30);



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire