Using ->with() with constraints, issue with 'HAVING'

Posted 3 years ago by domioanna

This code...

public function getStrategiesWithinDistance($strategy, $postcode, $distance)
    {
        $coords = Postcode::getCoordinates($postcode);
        $lat = $coords['latitude'];
        $lng = $coords['longitude'];

        return $this->user->$strategy()->with([
            'property' => function ($query) use ($lat, $lng, $distance) {
                $query
                    ->selectRaw(
                    '( 3959 * acos(
                            cos( radians( '.$lat.' ))
                            * cos( radians(latitude ) )
                            * cos( radians(longitude ) - radians( '.$lng.' ) )
                            + sin( radians('.$lat.') )
                            * sin( radians( latitude ) )
                        ))
                        AS distance')
                    ->having('distance', '<', $distance)
                    ->select('*');
            }
        ])->get();
    }

...is causing this error...

Illuminate \ Database \ QueryException (42S22)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select * from `properties` where `properties`.`deleted_at` is null and `properties`.`id` in (5, 6, 1, 2) having `distance` < 5)

Has anyone attempted to incorporate something like this and succeeded or can anyone see what could be the problem?

As you can probably see from my recent posts, this is something I'm REALLY struggling with.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.