Laravel项目根据前端参数获取指定天数的数据库帖子数据求助

Hey there! Let's get this date filtering sorted for your Laravel project. The key here is leveraging Laravel's built-in date helpers (which handle timezone config automatically) instead of relying solely on database functions like CURDATE()—this avoids common timezone mismatches that might have been causing your earlier issues.

Step 1: Basic Controller Logic #

First, here's a clean implementation using Eloquent (assuming you have a Post model mapped to your posts table):

use App\Models\Post;
use Illuminate\Http\Request;

public function fetchPosts(Request $request)
{
    // Validate the incoming day parameter first
    $validated = $request->validate([
        'day' => 'required|integer|in:1,2',
    ]);

    $day = $validated['day'];
    $postsQuery = Post::query();

    switch ($day) {
        case 1:
            // Fetch posts created TODAY
            $postsQuery->whereDate('created_at', today());
            break;
        case 2:
            // Fetch posts created YESTERDAY + TODAY
            $postsQuery->whereBetween('created_at', [
                now()->subDay()->startOfDay(),
                now()->endOfDay()
            ]);
            break;
    }

    // Execute the query and return results (adjust response format as needed)
    $posts = $postsQuery->get();
    return response()->json(['posts' => $posts]);
}

Step 2: Alternative Implementations #

Option A: Using Raw Database Queries (if you prefer DB facade) #

If you're working directly with the DB facade instead of Eloquent, here's how to do it:

use Illuminate\Support\Facades\DB;

public function fetchPosts(Request $request)
{
    $day = $request->input('day');
    if (!in_array($day, [1,2])) {
        return response()->json(['error' => 'Invalid day value'], 400);
    }

    $posts = DB::table('posts')
        ->when($day == 1, function ($query) {
            $query->whereDate('created_at', today());
        })
        ->when($day == 2, function ($query) {
            $query->whereBetween('created_at', [
                now()->subDay()->startOfDay(),
                now()->endOfDay()
            ]);
        })
        ->get();

    return response()->json(['posts' => $posts]);
}

Option B: Explicit Date Range Checks #

For day=2, you can also split the condition into two whereDate clauses (same result, just different syntax):

$postsQuery->whereDate('created_at', '>=', now()->subDay()->startOfDay())
           ->whereDate('created_at', '<=', now()->endOfDay());

Key Notes to Avoid Issues #

• Timezone Consistency: Ensure your Laravel config/app.php timezone (e.g., 'Asia/Shanghai') matches your database server's timezone. Laravel's today() and now() helpers use your app's configured timezone, which prevents mismatches with CURDATE()/NOW() (which use the database's timezone).
• Field Type: Make sure your posts table has a datetime or timestamp column (usually created_at) to store the post creation time.
• Validation: Always validate the day parameter to avoid unexpected inputs breaking your query.

内容的提问来源于stack exchange，提问作者Gowtham A Satheesh