Laravel视图共享SQL语法错误：未读消息统计查询异常

The core issue here is that your View Composer runs every time any view is loaded—including when no user is logged in. When there's no authenticated user, Auth::id() returns null, which gets inserted into your raw SQL query as an empty value. That's why your error shows broken syntax like messagesnews.to = where users.id !=—those parts are missing the user ID entirely.

In your controller, you probably only run this query when you know the user is logged in, so you never hit this problem there.

Fix 1: Add Authentication Check + Parameter Binding #

First, we'll add a check to ensure we only run the query if the user is authenticated. We'll also use parameter binding to avoid SQL injection and syntax issues (never concatenate user input directly into raw SQL!).

Modify your View Composer's boot method like this:

public function boot()
{
    View::share('chatusers', function () {
        // Exit early if no user is logged in
        if (!Auth::check()) {
            return [];
        }

        $userId = Auth::id();

        // Use parameter binding with ? placeholders
        $chatusers = DB::select("
            select users.id, users.name, users.email, count(is_read) as unread 
            from users 
            LEFT JOIN messagesnews ON users.id = messagesnews.from and is_read = 0 and messagesnews.to = ? 
            where users.id != ? 
            group by users.id, users.name, users.email
        ", [$userId, $userId]);

        return $chatusers;
    });
}

Fix 2: Use Laravel's Query Builder (Even Better!) #

For cleaner, more maintainable code (and automatic parameter binding), switch to Laravel's query builder instead of raw SQL:

public function boot()
{
    View::share('chatusers', function () {
        if (!Auth::check()) {
            return [];
        }

        $userId = Auth::id();

        $chatusers = DB::table('users')
            ->leftJoin('messagesnews', function ($join) use ($userId) {
                $join->on('users.id', '=', 'messagesnews.from')
                     ->where('messagesnews.is_read', 0)
                     ->where('messagesnews.to', $userId);
            })
            ->where('users.id', '!=', $userId)
            ->select('users.id', 'users.name', 'users.email', DB::raw('count(messagesnews.is_read) as unread'))
            ->groupBy('users.id', 'users.name', 'users.email')
            ->get();

        return $chatusers;
    });
}

Bonus: Blade Template Safety #

Your existing blade code will work fine with these fixes because when no user is logged in, $chatusers will be an empty array, so the loop won't run. The unread count will stay at 0, and the pending indicator will behave as expected.

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