Laravel获取用户预约列表仅首条带Post详情的问题求助

场景说明 #

需要获取指定用户的预约列表，Appointment表包含user_id与post_id字段，要求每条预约返回date、time、day、post_name、post_place信息，但当前返回结果中仅第一条预约带有name和place字段，其余预约缺失该信息。

原代码与返回结果 #

控制器代码 #

public function index()
{
    // Retrieve all appointments from the user
    $appointments = Appointment::where('user_id', Auth::user()->id)->get();

    // Retrieve all post IDs associated with the appointments
    $postIds = $appointments->pluck('post_id')->toArray();

    // Retrieve all posts with the associated post IDs
    $posts = Post::whereIn('id', $postIds)->get()->keyBy('id');

    // Assign post details to each appointment
    $appointments = $appointments->map(function ($appointment) use ($posts) {
        $postId = $appointment->post_id;

        if (isset($posts[$postId])) {
            $post = $posts[$postId];
            $appointment->name = $post->name;
            $appointment->place = $post->place;
        } else {
            // For debugging purposes, log the missing post details
            Log::info("Post details not found for appointment: " . $appointment->id);
        }
        return $appointment;

    });

    return $appointments;

}

Postman返回结果 #

[
    {
        "id": 4,
        "user_id": 9,
        "post_id": 2,
        "date": "6/14/2023",
        "day": "Wednesday",
        "time": "12:00 PM",
        "status": "upcoming",
        "created_at": "2023-06-12T17:19:58.000000Z",
        "updated_at": "2023-06-12T17:19:58.000000Z",
        "name": "Biskra",
        "place": "Tolga"
    },
    {
        "id": 5,
        "user_id": 9,
        "post_id": 9,
        "date": "6/24/2023",
        "day": "Saturday",
        "time": "14:00 PM",
        "status": "cancel",
        "created_at": "2023-06-12T18:53:45.000000Z",
        "updated_at": "2023-06-12T18:53:45.000000Z"
    },
    {
        "id": 6,
        "user_id": 9,
        "post_id": 8,
        "date": "6/17/2023",
        "day": "Saturday",
        "time": "12:00 PM",
        "status": "complete",
        "created_at": "2023-06-13T01:43:14.000000Z",
        "updated_at": "2023-06-13T01:43:14.000000Z"
    }
]

问题原因 #

原代码通过手动查询Post并keyBy('id')的方式关联数据，存在两个核心问题：

1. Post记录不存在：如果post_id对应的Post记录在数据库中已被删除，isset($posts[$postId])会返回false，无法赋值name和place。
2. 数据类型不匹配：若Appointment的post_id是字符串类型，而Post的id是整数类型，keyBy('id')后的数组键是整数，用字符串$postId匹配会失败，导致无法找到对应Post。

解决方案 #

方案一：使用Eloquent关联（推荐） #

利用Laravel的Eloquent关联关系，自动处理关联查询，同时避免N+1性能问题。

1. 在Appointment模型中定义与Post的关联：

// app/Models/Appointment.php
public function post()
{
    return $this->belongsTo(Post::class);
}

2. 修改控制器代码，预加载关联并整理返回字段：

public function index()
{
    $appointments = Appointment::where('user_id', Auth::user()->id)
        ->with('post') // 预加载关联，避免N+1查询
        ->get()
        ->map(function ($appointment) {
            return [
                'id' => $appointment->id,
                'user_id' => $appointment->user_id,
                'post_id' => $appointment->post_id,
                'date' => $appointment->date,
                'day' => $appointment->day,
                'time' => $appointment->time,
                'status' => $appointment->status,
                'created_at' => $appointment->created_at,
                'updated_at' => $appointment->updated_at,
                'name' => $appointment->post?->name ?? '', // 空值兜底，确保字段存在
                'place' => $appointment->post?->place ?? ''
            ];
        });

    return $appointments;
}

方案二：使用数据库左连接 #

直接通过SQL左连接查询，在数据库层面完成关联，确保每条预约都能返回name和place字段（即使Post不存在也会返回空值）。

public function index()
{
    $appointments = Appointment::select(
            'appointments.*',
            'posts.name as name',
            'posts.place as place'
        )
        ->leftJoin('posts', 'appointments.post_id', '=', 'posts.id')
        ->where('appointments.user_id', Auth::user()->id)
        ->get();

    return $appointments;
}

这两种方案都能保证所有预约记录返回name和place字段，即使对应的Post不存在，也会返回空值而非缺失字段，完全符合需求。

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