Laravel 6.0 多表关联查询：按日期范围获取聚合型帖子列表实现方案

Hey there! Let's work through how to get that aggregated list of posts filtered by a date range, pulling in the correct post details based on post_type. Here's a step-by-step breakdown:

1. Understand the Core Requirements #

We need to:

• Filter post_category records (or their linked posts) within a specified date range
• Dynamically load the corresponding post data (text/photo/video) based on the post_type value
• Format the results into a unified list that includes all relevant post details

2. Query Implementation with Efficient Loading #

First, let's write the query to fetch the filtered post_category records, and load only the necessary post relation for each entry (to avoid unnecessary database calls):

Option 1: Filter by PostCategory's Created Date #

If you want to filter based on when the category association was created:

use App\PostCategory;
use Carbon\Carbon;

// Define your date range (adjust these values as needed)
$startDate = Carbon::parse('2024-01-01')->startOfDay();
$endDate = Carbon::now()->endOfDay();

// Fetch filtered post categories, then load the correct post relation
$postCategories = PostCategory::whereBetween('created_at', [$startDate, $endDate])
    ->get()
    ->map(function ($category) {
        // Determine which relation to load based on post_type
        $relation = null;
        switch($category->post_type) {
            case 1: $relation = 'text'; break;
            case 2: $relation = 'photo'; break;
            case 3: $relation = 'video'; break;
        }

        if (!$relation) return null;

        // Load the specific relation for this category
        $category->load($relation);
        $post = $category->$relation;

        // Format the unified result
        return [
            'category_id' => $category->category_id,
            'post_type' => $category->post_type,
            'post_id' => $post->id,
            'title' => $post->title,
            'content' => $category->post_type === 1 ? $post->content : null,
            'image' => $category->post_type === 2 ? $post->image : null,
            'video_source_url' => $category->post_type === 3 ? $post->video_source_url : null,
            'created_at' => $post->created_at->toDateTimeString(),
        ];
    })
    ->filter(); // Remove any entries where no post was found

Option 2: Filter by the Post's Created Date #

If you need to filter based on when the actual post was created (instead of the category association), use whereHas to check the linked post's date:

$postCategories = PostCategory::where(function ($query) use ($startDate, $endDate) {
        // Check each post type's created_at date
        $query->whereHas('text', function ($q) use ($startDate, $endDate) {
                $q->whereBetween('created_at', [$startDate, $endDate]);
            })
            ->orWhereHas('photo', function ($q) use ($startDate, $endDate) {
                $q->whereBetween('created_at', [$startDate, $endDate]);
            })
            ->orWhereHas('video', function ($q) use ($startDate, $endDate) {
                $q->whereBetween('created_at', [$startDate, $endDate]);
            });
    })
    ->get()
    ->map(function ($category) {
        // Same mapping logic as Option 1 here
        $relation = null;
        switch($category->post_type) {
            case 1: $relation = 'text'; break;
            case 2: $relation = 'photo'; break;
            case 3: $relation = 'video'; break;
        }

        if (!$relation) return null;

        $category->load($relation);
        $post = $category->$relation;

        return [
            'category_id' => $category->category_id,
            'post_type' => $category->post_type,
            'post_id' => $post->id,
            'title' => $post->title,
            'content' => $category->post_type === 1 ? $post->content : null,
            'image' => $category->post_type === 2 ? $post->image : null,
            'video_source_url' => $category->post_type === 3 ? $post->video_source_url : null,
            'created_at' => $post->created_at->toDateTimeString(),
        ];
    })
    ->filter();

3. Key Explanations #

• switch(): Used to dynamically select the correct Eloquent relation based on post_type (works perfectly for Laravel 6.0's PHP version support).
• load(): Lazy loads only the necessary relation for each category entry, which is more efficient than eager loading all three relations upfront.
• map(): Transforms each PostCategory record into a unified array structure that includes both category and post data.
• filter(): Removes any entries where the linked post wasn't found (in case of orphaned post_category records).

4. Usage Example #

You can return this formatted list as JSON from a controller:

return response()->json([
    'success' => true,
    'posts' => $postCategories
]);

This will give you a clean, aggregated list matching the structure you referenced, with all relevant post details included based on their type.

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