Giải quyết vấn đề sử dụng paginate kết hợp mệnh đề having trong Laravel

Đặt vấn đề

Chào các bạn, ở bài viết này mình sẽ đề cập đến một lỗi mà có lẽ mọi người hay gặp khi làm việc với query trong Laravel. Mình nói sơ qua về ngữ cảnh bài toán đơn giản như thế này: Mình có table shops chứa thông tin cơ bản của một shop như tên, hình ảnh, ngày thành lập. Table locations chứa name, country, city, latitude và longitude. Một shops có một locations, và bài toán đặt ra là với một locations (lat, lng) và radius truyền lên, mình cần liệt kê những shops gần điểm truyền lên nhất và nằm trong bán kính đó, danh sách trả về có thông tin shops kèm khoảng cách và tất nhiên là có phân trang. Khá đơn giản phải không nào, triển thôi.

Giải pháp

Đầu tên mình xây dựng một scope trong model Location như sau:

    public function scopeDistance($query, $lat, $lng, $radius = 100, $unit = "km")
    {
        $unit = ($unit === "km") ? 6378.10 : 3963.17;
        $lat = (float) $lat;
        $lng = (float) $lng;
        $radius = (double) $radius;

        return $query->having('distance', '<=', $radius)
            ->select(DB::raw("*,
                ($unit * ACOS(COS(RADIANS($lat))
                    * COS(RADIANS(latitude))
                    * COS(RADIANS($lng) - RADIANS(longitude))
                    + SIN(RADIANS($lat))
                    * SIN(RADIANS(latitude)))) AS distance")
            )->orderBy('distance');
    }

Ở đây mình có sử dụng công thức tính khoảng cách giữa 2 locations (latitude, longitude)

acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(long2−long1)).R
R is earth’s radius (mean radius = 6,371km);

Trong repository mình sử dụng scope này.

return app(Location::class)->distance($latitude, $longitude, $radius)
            ->join('shops', 'shop_id', '=', 'shops.id')
            ->select('shops.*',  'distance')
            ->paginate(10);

Chạy ==> Lỗi

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from `locations` inner join `shops` on `shop_id` = `shops`.`id` where `locations`.`deleted_at` is null having `distance` <= 100)"

Lỗi này xuất hiện khi chúng ta sử dụng query builder với mệnh đề having ở trước sau đó paginate thì nó sẽ sinh ra lỗi sql, hiểu đơn giản lỗi này là do biến được sử dụng trong mệnh đề having không nằm trong list select. Để giải quyết vấn đề này mình chuyển sang sử dụng where thay vì mệnh đề having, mình sửa lại cái scope trong model Location như sau:

public function scopeDistance($query, $lat, $lng, $radius = 100, $unit = "km")
    {
        $unit = ($unit === "km") ? 6378.10 : 3963.17;
        $lat = (float) $lat;
        $lng = (float) $lng;
        $radius = (double) $radius;

        $sql = "*, ($unit * ACOS(COS(RADIANS($lat))
                * COS(RADIANS(latitude))
                * COS(RADIANS($lng) - RADIANS(longitude))
                + SIN(RADIANS($lat))
                * SIN(RADIANS(latitude)))) AS distance";

        $query->getQuery()->selectRaw($sql);
        $rawQuery = $this->getSql($query);

        return DB::table(DB::raw("(" . $rawQuery . ") as item"))
            ->orderBy('distance')
            ->where('distance', '<', $radius);
    }
    
/**
     * @param Builder $builder
     * @return string
    */
    private function getSql($builder)
    {
        $sql = $builder->toSql();

        foreach($builder->getBindings() as $binding) {
            $value = is_numeric($binding) ? $binding : "'" . $binding . "'";
            $sql = preg_replace('/\?/', $value, $sql, 1);
        }

        return $sql;
    }

OK, vấn đề đã được giải quyết. Tất nhiên đó chỉ là một trong số những cách để giải quyết vấn đề nên trên, bạn có thể sử dụng offset và limit hoặc tận dụng Illuminate\Pagination\Paginator để phân trang. Nhưng mình nghĩ giải pháp của mình có vẻ đơn giản mà hiệu quả nhất.

Kết luận

Hi vọng bài viết này sẽ giúp ích gì đó cho những bạn gặp lỗi tương tự mà chưa tìm ra giải pháp, cảm ơn các bạn đã đọc.