Column not found: 1054 Unknown column 'max_distance' in 'order clause

• while using count $total_shops = $query->count() error coming i dont know how i can manage or solve it this is my full code

public function ShopsListAjax(Request $request) { $page = $request->input('page', 1); $perPage = 2; $distance = null; $shop_category = null; $shop_category = $request->input('shop_category'); $sort_by = null; $orderby = null; $sorting = null; $dist_min_range = null; // Define these variables $dist_max_range = null; // Define these variables $distanceRanges = [ '1-10' => ['min' => 0, 'max' => 10], '1-25' => ['min' => 0, 'max' => 25], '1-100' => ['min' => 0, 'max' => 100], '1-9999' => ['min' => 0, 'max' => 9999] ]; $user = User::where('id', Auth::id())->first(); $userLat = null; $userLong = null; if (!empty($_COOKIE['user_latitude']) && !empty($_COOKIE['user_longitude'])) { $userLat = $_COOKIE['user_latitude']; $userLong = $_COOKIE['user_longitude']; } else { // Get the authenticated user $user = User::where('id', Auth::id())->first();

        if ($user) {
            $userLat = $user->latitude ?? null;
            $userLong = $user->longitude ?? null;
        } else {
            // Check if latitude and longitude are already stored in the session
            if (session()->has('userLat') && session()->has('userLong')) {
                $userLat = session('userLat');
                $userLong = session('userLong');
            } else {
                $validUserAgentPattern = '/^(Mozilla|Opera|Edge|Internet Explorer|AppleWebKit|Chrome|Safari|Trident)/i';

                if (preg_match($validUserAgentPattern, $request->userAgent())) {
                    // Check if the IP record exists in the ip_locations table
                    $ipToCheck = $request->ip();
                    // $ipToCheck = "182.180.151.216";
                    // $ipLocation = IPLocations::where('ip_address', $ipToCheck)->first();
                    $apiData = IpLocationService::fetchIpLocationFromApi($ipToCheck);
                    if ($apiData) {
                        // Store latitude and longitude in session
                        $userLat = $apiData['latitude'] ?? null;
                        $userLong = $apiData['longitude'] ?? null;
                        session(['userLat' => $userLat, 'userLong' => $userLong]);
                    }
                }
            }
        }
    }
    if ($request->distance != '') {
        $distance = $request->distance;
    }
    if ($request->shop_category != '') {
        $shop_category = $request->shop_category;
    }
    if ($request->sort_by != '') {
        $sort_by = $request->sort_by;
    }
    if ($distance != '' || $shop_category != '' || $sort_by != '') {
        $whereClause = function ($query) use ($distance, $shop_category) {
            $distanceRanges = [
                '1-10' => ['min' => 0, 'max' => 10],
                '10-25' => ['min' => 10, 'max' => 25],
                '25-100' => ['min' => 25, 'max' => 100],
                '100-9999' => ['min' => 100, 'max' => 9999]
            ];
            if (is_array($shop_category)) {
                $query->whereIn('shops.shop_category', $shop_category);
            } elseif (!empty($shop_category)) {
                $query->where('shops.shop_category', $shop_category);
            }
        };
        if ($sort_by != '' && $sort_by == 'shop_rate') {
            $orderby = 'shops.ratings';
            $sorting = 'desc';
        }
        if ($sort_by != '' && $sort_by == 'rating_desc') {
            $orderby = 'avg_star_rating';
            $sorting = 'desc';
        }
        if ($sort_by != '' && $sort_by == 'distance') {
            $orderby = 'max_distance';
            $sorting = 'asc';
        }
    } else {
        $whereClause = function ($query) {
            $query->whereRaw('1 = 1');
        };
    }

    // Check if distance filter is applied
    if ($distance != '' && isset($distanceRanges[$distance])) {
        $range = $distanceRanges[$distance];
        $dist_min_range = $range['min'];
        $dist_max_range = $range['max'];
    }
    // Start building the base query
    $query = DB::table('shops')
        ->select(
            'users.id as user_id',
            'users.name as first_name',
            'users.last_name',
            'users.username',
            'users.is_contributor',
            'users.likes',
            'states.code as state_code',
            'users.profile_img',
            'users.experience',
            // 'shops.*',
            'shops.name',
            'shops.address',
            'shops.city',
            'shops.slug',
            'shops.shop_category',
            'shops.poc_image',
            'shops.zip_code',
            'shops.shop_details',
            'shops.lattitude as shop_latitude',
            'shops.longitude as shop_longitude',
            'shops.make_id as make_id',
            DB::raw('( 3959 * acos( cos( radians(' . $userLat . ') ) * cos( radians( shops.lattitude ) ) * cos( radians( shops.longitude ) - radians(' . $userLong . ') ) + sin( radians(' . $userLat . ') ) * sin( radians( shops.lattitude ) ) ) ) AS max_distance'), // Dynamic distance calculation in miles
            DB::raw('AVG(shop_reviews.star_ratings) AS avg_star_rating'),
            DB::raw('COUNT(shop_reviews.star_ratings) AS rating_count')
        )
        ->join('users', 'shops.user_id', '=', 'users.id')
        ->leftJoin('states', 'shops.state', '=', 'states.id')
        ->join('vehicle_makes', 'vehicle_makes.id', '=', 'shops.make_id')
        ->leftJoin('shop_reviews', 'shops.id', '=', 'shop_reviews.shop_id')
        ->where('shops.role_id', '=', '4')
        ->where('shops.status', '=', '1')
        ->where('users.status', '=', 1)
        ->whereNull('shops.deleted_at')
        ->groupBy('shops.id', 'shops.user_id');
    $totalShops = $query->count();

    // dd(vsprintf(str_replace(array('?'), array('\'%s\''), $query->toSql()), $query->getBindings()));
    // Subquery to filter shops of category 2 within 10 miles
    $subquery = DB::table('shops')
        ->where('shops.shop_category', '=', 2)
        ->select('shops.id as new_id')
        ->whereRaw('( 3959 * acos( cos( radians(' . $userLat . ') ) * cos( radians( shops.lattitude ) ) * cos( radians( shops.longitude ) - radians(' . $userLong . ') ) + sin( radians(' . $userLat . ') ) * sin( radians( shops.lattitude ) ) ) ) <= shops.mobile_shop_dist');

    // Apply filtering conditions
    if ($userLat !== null && $userLong !== null && $dist_min_range !== null && $dist_max_range !== null) {
        $query->havingRaw('max_distance BETWEEN ? AND ?', [$dist_min_range, $dist_max_range]);
        // $query->where('shops.shop_category', 1);
    }
    if ($whereClause != "") {
        $query = $query->where($whereClause);
    }
    if ($request->make != "null") {
        $query = $query->whereRaw("FIND_IN_SET($request->make, shops.make_id) > 0");
    }
    // Filtering based on shop category
    if ($shop_category == 1) {
        // traditional shops
        $query->where('shops.shop_category', 1)
            ->groupBy('shops.id', 'shops.user_id');
    } else if ($shop_category == 2) {
        // mobile shops
        $query->where('shops.shop_category', 2)
            ->groupBy('shops.id', 'shops.user_id');
    }

    // dd(vsprintf(str_replace(array('?'), array('\'%s\''), $query->toSql()), $query->getBindings()));
    // $data = $query->groupBy('users.id')->get();
    $data = $query
        ->where(function ($query) use ($subquery) {
            $query->where('shops.shop_category', '=', 1) // For category 1, no distance filtering
                ->orWhereIn('shops.id', $subquery); // For category 2, apply subquery to filter within 10 miles
        })->get();


    if (!$data->isEmpty()) {
        $make_data = $data[0]->make_id;
        $make_id_explode = explode(',', $make_data);
        for ($i = 0; $i < count($make_id_explode); $i++) {
            $data[0]->make_id_explode[$i] = DB::table('vehicle_makes')
                ->where('id', '=', $make_id_explode[$i])
                ->select(
                    'name as experties',
                )
                ->get();
        }
    }

    if ($orderby != null) {
        $query->orderBy($orderby, $sorting);
    }
    $data = $query->skip(($page - 1) * $perPage)->take($perPage)->get();
    // $totalShops = 9;
    $hasMorePages = $totalShops > $page * $perPage;

    return response()->json([
        'html' => view('ajax-shop-list', ['data' => $data, 'hasMorePages' => $hasMorePages])->render(),
        'mobhtml' => view('ajax-shop-list-mobile', ['data' => $data, 'hasMorePages' => $hasMorePages])->render(),
        'hasMorePages' => $hasMorePages,
    ]);
}