Count with GroupBy not working in Laravel

Question

I want the count of Chats between users and Group By their Chat Id. But the query does not give the count of chats. Its blank. I have seen the similar problems in Stack Overflow (Laravel Eloquent groupBy() AND count , Laravel get count of results based on groupBy ) and used those queries which are marked as correct but still not getting the desired result.

I am using the following query:

$user_info = DB::table('chat_messages')
                ->select(DB::raw('count(*) as total'))
                ->where('viewed','=', '0')
                ->groupBy('chat_id','type')
                ->get();

My table structure is as:

{
"_id": ObjectId("571f549b1c8cb6972c8b4567"),
"message": "Testing Chat Functionality",
"type": "singlechat",
"date": "2016-04-26 11:44:27",
"from": "56b07a5a083f119a0b8b4569",
"to": "56b07a5a083f119a0b8b4569",
"chat_id": "56f65fc51c8cb6ca1a8b4567",
"status": "1",
"updated_at": ISODate("2016-04-26T11:44:27.624Z"),
"created_at": ISODate("2016-04-26T11:44:27.624Z"),
"viewed": "0"
}

And the result comes like this:

Array
(
[0] => Array
    (
        [_id] => Array
            (
                [chat_id] => 56f65fc51c8cb6ca1a8b4567
                [type] => singlechat
            )

        [chat_id] => 56f65fc51c8cb6ca1a8b4567
        [type] => singlechat
        [count(*) as total] => 
    )

[1] => Array
    (
        [_id] => Array
            (
                [chat_id] => 56fa3f5f1c8cb667138b4567
                [type] => groupchat
            )

        [chat_id] => 56fa3f5f1c8cb667138b4567
        [type] => groupchat
        [count(*) as total] => 
    )

)

Answer 1

$user_info = DB::table('chat_messages')
                ->where('viewed','=', '0')
                ->select(DB::raw('count(chat_messages.*) as total'))
                ->groupBy('chat_id','type')
                ->get();

This should work, I'm unable to test right now

Answer 2

try this code.

 $this->select(\DB::raw("*, count(*) as total"))
->where('viewed', "0")
->groupBy(["chat_id", "type"]);