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.
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.
