CODEWITHSUNDEEP
mysqllaravel-5
Ronald Torres
Ronald Torres

Reputation: 199

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();

Here is my code when i tried to insert the users avatar but i get this error: users.avatar' isn't in GROUP BY

        $chat = DB::table('chats')
             ->join('users', 'users.id', '=', 'chats.user_id')
             ->select('users.avatar','user_id', DB::raw('count(*) as total'))
             ->where('admin_id', Auth::user()->id)
             ->where('status',2)
             ->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.

Upvotes: 1

Views: 15587

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

If you GROUP BY the id in the users table, you should be able to select any other column in the users table as well:

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

At first site, this might seem to be a violation of the rules of GROUP BY, which state that non aggregate columns cannot be selected. But this is allowed because the user id functionally determines the values of the other columns. In other words, if we select a user id, we know precisely what his avatar value must be.

If the above gives you an error, you may try this GROUP BY:

->groupBy('users.id', 'users.avatar')

Upvotes: 5

Related Questions