I have a query of MySQL but I need to convert it into an eloquent model laravel 8. The query is given below,
$query = "SELECT group_id FROM `chat_histories` join chat_group on chat_group.id = chat_histories.group_id where chat_group.is_group = 1 and chat_histories.created_at BETWEEN '$startDate' and '$endDate' and chat_histories.deleted_at is null group by group_id";
$query = "select count(group_id) as total_chat_thread from ($query) total_chat";
DB::select($query);
So far i have done this,
ChatHistory::leftJoin('chat_group', 'chat_group.id', '=', 'chat_histories.group_id')
->selectRaw('count(*) as totals')
->where('chat_group.is_group', 1)
->whereBetween('chat_histories.created_at', [$startDate, $endDate])
->groupBy('chat_histories.group_id')
->count('totals');
But this returns a list, but I need that count of the list. That means it's showing 22 rows, I need that 22 as return.
My Model ChatHistory relation with ChatGroup
public function chatGroup() {
return $this->belongsTo(ChatGroup::class, 'group_id', 'id');
}
My Model ChatGroup relation with ChatHistory
public function chatHistory() {
return $this->hasMany(ChatHistory::class,'group_id','id');
}
Please help to convert it into an eloquent model query Thanks in advance.
If you have the model Group with a relation
history
hasMany. it should be like this.You dont need the
whereNull
, if the model ChatHistory has softDelete enabled.