Laravel 5：解决标记特定用户帖子通知为已读的SQL错误

Hey there! The error you're hitting comes from trying to access a JSON field with syntax your database doesn't recognize as a valid column reference. Let's fix this and get your notification marking working smoothly.

The Root Issue #

Your notifications table's data column stores JSON content (like {"post_id":8,"title":"Example Post...}), but your original code uses where("data['post_id']", $post->id) — SQL reads data['post_id'] as a literal column name that doesn't exist in your table. That's exactly why you get the Unknown column error.

Solution 1: Use Laravel's whereJsonContains (Cross-Database Compatible) #

Laravel has a built-in method to query JSON fields that works across MySQL, PostgreSQL, and more. Here's the adjusted code:

public function show(Post $post) { 
    // Mark ALL unread notifications linked to this post as read
    auth()->user()->unreadNotifications()
        ->whereJsonContains('data->post_id', $post->id)
        ->update(['read_at' => now()]); 

    return view('post.show', compact('post')); 
}

Solution 2: Database-Specific JSON Syntax (MySQL Example) #

If you're using MySQL, you can directly use its native JSON path operator -> to target the post_id inside the data column:

public function show(Post $post) { 
    auth()->user()->unreadNotifications()
        ->where('data->post_id', $post->id)
        ->update(['read_at' => now()]); 

    return view('post.show', compact('post')); 
}

Key Improvements Over Your Original Code #

• No more first(): Ditching first() lets you update all matching unread notifications at once, not just the first one. This makes more sense if a user might have multiple notifications for the same post.
• Error safety: If there are no matching notifications, update() will just return 0 instead of throwing a null reference error (which would happen if you called first()->update() when no results exist).

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