How to prevent an unfiltered query on millions of records?

To prevent unfiltered queries on a large dataset, you can implement a default filter that ensures a limited date range is always applied. If the user does not specify a date range, you can set a default date range in your query. Here's a step-by-step solution using Laravel's Eloquent ORM as an example:

1. Set Default Date Range: Define a default date range that will be used if the user does not provide one.

2. Apply the Filter in the Query: Modify your query to check if the date range is provided. If not, use the default date range.

Here's an example implementation:

use Carbon\Carbon;
use Illuminate\Http\Request;
use App\Models\YourModel;

public function index(Request $request)
{
    // Define the default date range (e.g., last 30 days)
    $defaultStartDate = Carbon::now()->subDays(30);
    $defaultEndDate = Carbon::now();

    // Get the date range from the request, or use the default date range
    $startDate = $request->input('start_date', $defaultStartDate);
    $endDate = $request->input('end_date', $defaultEndDate);

    // Ensure the date range is within a reasonable limit
    if (Carbon::parse($startDate)->diffInDays(Carbon::parse($endDate)) > 30) {
        return response()->json(['error' => 'Date range too large. Please select a range within 30 days.'], 400);
    }

    // Query the database with the date range filter
    $records = YourModel::whereBetween('created_at', [$startDate, $endDate])->get();

    return response()->json($records);
}

Explanation:

1. Default Date Range: We define a default date range of the last 30 days using Carbon.

2. Request Date Range: We retrieve the start_date and end_date from the request. If they are not provided, we use the default date range.

3. Date Range Validation: We ensure the date range does not exceed a reasonable limit (e.g., 30 days). If it does, we return an error response.

4. Query with Date Range: We use the whereBetween method to filter records based on the date range.

This approach ensures that your query always has a date range filter applied, preventing unfiltered queries on millions of records. Adjust the default date range and validation logic as needed for your specific use case.