CODEWITHSUNDEEP
laravellaravel-query-builder
user1849169
user1849169

Reputation:

How in laravel query builder, get count of child rows that meet a certain condition, for each record in parent table?

I have a users table and a messages table that stores messages that users send to each other. users table:

-------------------
|id|name  |lastName|
|------------------|
|1 |emma  |watson  |
|2 |taylor|swift   |
|3 |tiger |woods   |

massages table:

|id|messageBody|from_id|to_id|isRead|
|-----------------------------------|
|1 |...        |1      |2    |0     |
|2 |...        |2      |3    |1     |
|3 |...        |1      |3    |0     |

what I seek is to get the list of all users and count of unread messages that they have send to current user,which in laravel is reached by auth()->user(). I realized that sql query for this is sth like this:

SELECT users.id,users.name,users.lastName,(select count(*)
from messages where messages.from_id = users.id and messages.to_id =1 and 
messages.isRead = 0) as countOfNewMessages FROM users

but how can I reach this result with laravel's query builder?

Upvotes: 0

Views: 1062

Answers (2)

MD. Jubair Mizan
MD. Jubair Mizan

Reputation: 1560

On your Messages Model 

public function user(){
  return $this->belongsTo('App\User','to_id','id');
}

On your Controller 

//For All Data
$messages = Messages::with('user')->where(['isRead'=>0,'to_id'=>1])->get();
//For Count Data
you can easily check how much unread message by `count($messages)`

Upvotes: 0

Yur Gasparyan
Yur Gasparyan

Reputation: 714

The classic method is

// messages
$messages = Messages::where('isRead',0)->where('to_id',auth()->user()->id)->get();

//count of messages

$count = $messages->count();

But you also can use relations to get messages of users and their count. Please follow Laravel:Relations

Upvotes: 1

Related Questions