基于Laravel Eloquent实现可筛选可搜索的餐厅订单列表

Hey there! Let's fix up your order filtering function step by step, making sure it's secure, follows Eloquent best practices, and handles all your requirements properly.

Here's a polished, secure version of your filterOrders function that adheres to your logic flow and handles edge cases:

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

public function filterOrders(Request $request)
{
    // 1. Fetch and sanitize request parameters with sensible defaults
    $searchQuery = trim($request->input('searchQuery', ''));
    $dateRange = $request->input('dateRange', [
        Carbon::now()->startOfMonth()->toDateString(),
        Carbon::now()->toDateString()
    ]);
    $providerId = $request->input('provider_id');
    $paid = $request->boolean('paid');
    $notPaid = $request->boolean('notPaid');
    $perPage = $request->input('perPage', 25);

    // 2. Initialize Eloquent query builder with default sorting
    $query = Order::with('provider') // Preload provider to avoid N+1 queries
        ->orderBy('created_at', 'asc');

    // 3. Step 1: Filter by date range (highest priority)
    if (is_array($dateRange) && count($dateRange) === 2) {
        $startDate = Carbon::parse($dateRange[0])->startOfDay();
        $endDate = Carbon::parse($dateRange[1])->endOfDay();
        $query->whereBetween('order_paid', [$startDate, $endDate]);
    }

    // 4. Step 2: Apply other filters (provider ID, payment status)
    // Filter by provider ID if provided
    if ($providerId) {
        $query->where('provider_id', $providerId);
    }

    // Handle payment status combinations
    $paymentFilters = [];
    if ($paid && !$notPaid) {
        $paymentFilters[] = ['order_paid', '!=', null];
    } elseif (!$paid && $notPaid) {
        $paymentFilters[] = ['order_paid', '=', null];
    }
    // Only add filters if we have specific conditions
    if (!empty($paymentFilters)) {
        $query->where($paymentFilters);
    }

    // 5. Step 3: Apply search query (final step)
    if (!empty($searchQuery)) {
        $query->where(function ($subQuery) use ($searchQuery) {
            $subQuery->where('id', 'like', "%{$searchQuery}%")
                ->orWhereHas('provider', function ($providerQuery) use ($searchQuery) {
                    $providerQuery->where('name', 'like', "%{$searchQuery}%");
                })
                ->orWhereRaw('DATE(order_paid) LIKE ?', ["%{$searchQuery}%"]);
        });
    }

    // 6. Paginate results
    return $query->paginate($perPage);
}

Let's break down why this works better for your use case:

1. Safe Parameter Handling #

• Uses $request->boolean() to properly convert frontend input to boolean values for paid/notPaid
• Sets sensible defaults (e.g., default date range is the current month) to handle empty input
• Trims searchQuery to avoid accidental whitespace matches

2. Security & Eloquent Best Practices #

• Eliminates raw SQL injection risks by using Eloquent's built-in query methods and parameter binding (your original code had unescaped SQL concatenation, which is dangerous!)
• Uses with('provider') to preload the provider relationship, preventing N+1 database queries when accessing provider data

3. Strict Filter Order #

Follows your exact required flow:

1. Date range filter first
2. Provider ID and payment status filters next
3. Search query applied last to the pre-filtered dataset

4. Flexible Payment Status Logic #

• If both paid and notPaid are true (or both are false), no payment status filter is applied
• If only paid is true: returns orders with non-null order_paid
• If only notPaid is true: returns orders with null order_paid

5. Comprehensive Search Coverage #

Matches your search requirements:

• Order ID (partial matches allowed)
• Provider name (via relationship filtering)
• Payment date (extracts date part from order_paid for string matching)

• Never concatenate raw user input into SQL: Always use Eloquent methods or parameterized raw queries (like whereRaw('... ?', [$value]))
• Preload relationships: Use with() to load related models upfront—it's one of the easiest ways to boost query performance
• Leverage Carbon: Laravel's built-in Carbon library simplifies date parsing, manipulation, and formatting (no more manual string handling!)
• Group OR conditions: Wrap search-related OR clauses in a closure to avoid mixing them with AND filters (prevents unexpected logic bugs)
• Test edge cases: Verify behavior when:
  • No parameters are passed
  • Only one filter is applied
  • Multiple filters are combined
  • Search terms are partial matches or empty

内容的提问来源于stack exchange，提问作者mlvrkhn