使用Laravel Eloquent上的连接计算来自不同表的行数

How can I use eloquent to count rows of another table joined with the main table?

I have three tables:

  • clients
  • sms notification
  • email notification

I want to join them by pulling the client list first, then count how many times they had notification from both SMS and email. Finally returning the count or 0 if no notification has been sent.

enter image description here

I found a sample: Laravel Eloquent to join table and count related

I used this for emails:

<?php

$clients = Client::where('cl_delete_status', false)
    ->leftJoin('insura_communication_emails', 'insura_clients.cl_id', 'insura_communication_emails.cl_id')
    ->leftJoin('insura_communication_sms', 'insura_clients.cl_id', 'insura_communication_sms.cl_id')
    ->select('*', DB::raw('count(ice_id) as total'))
    ->groupBy('insura_clients.cl_client_id')
    ->orderBy('insura_clients.cl_id', 'desc')->get();

How can I put both tables that are email and SMS insura_communication_sms and in the code above? It skips those with 0 counts in the email notification.