QLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax – LARAVEL

Plz, can you help me, i get this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘where is_read = 0 ) as unread from messages left join users on users.id‘ at line 1 (SQL: select users.id, users.name, users.avatar, users.email, users.is_owner, messages.from, messages.created_at, (SELECT count(messages.is_read) where is_read = 0 ) as unread from messages left join users on users.id = messages.from where messages.to = 175 and users.id != 175 group by users.id order by messages.created_at desc)

and this is my query inside laravel

$my_id = Auth::user()->id;

 
   $users = DB::table('messages')->leftJoin('users' , 'users.id' , '=' , 'messages.from')
  
   ->select('users.id' , 'users.name' , 'users.avatar' , 'users.email', 'users.is_owner' , 'messages.from' , 'messages.created_at' , 
        DB::raw("(SELECT count(messages.is_read)  where is_read = 0  ) as unread"))
   ->where('messages.to' , $my_id)
   ->where('users.id' , '!=' , $my_id)
   ->groupBy('users.id' )
   ->orderBy('messages.created_at', 'desc')
   ->get();

And this is only problem at my live server, at my localhost (xampp) it works fine, plz can you help me, because i can’t see problem.

P.S. When i remove this part DB::raw("(SELECT count(messages.is_read) where is_read = 0 ) as unread")) it works fine, where is mistake here?

Answer

you are missing the table

It should be

SELECT COUNT(is_read) FROM messages WHERE is_read=0;

Advertisement

Answer