How to join 2 tables with group by query using laravel

i have a users and chats table in my database. Here are some sample data in my tables:

users table:

id  |   name  |    avatar
1       john     default.png
2       mark       picture.jpg

chats table:

   id  |   user_id  |  friend_id | message | status
    1        1           2           hello     0
    2        1           2           hi        1

the purpose of the status column is to determine whether the message is already read.

  status 0 = unread message
  status 1 = read message

Here is my code to group and count all the unread messages:

        $chat = DB::table('chats')
             ->join('users', 'users.id', '=', 'chats.user_id')
             ->select('user_id', DB::raw('count(*) as total'))
             ->where('friend_id', Auth::user()->id)
             ->where('status',0)
             ->groupBy('user_id')
             ->get();

What i want is to also get the avatar of the user who sent the messages. I don not know how to structure my query to get the avatar column. Any help would be appreciated. Thanks.

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2rRE80R
via IFTTT