Laravel leftJoin with closure join in relational table

Tables:

- salon_clients: id, salon_id, client_id
- clients: id, name, phone
- client_calls: id, salon_id, phone

Dynamic variable: $salonID

Logic (text):

I need to take all the calls from client_calls for a specific salon based on salon_id and phone parameters, do a leftjoin by phone in clients table to take the client name if there is a match and also in leftjoin closure do a innerjoin to check if the client matches salon_client table from that salon by clients.id = salon_clients.client_id where user_id = $userID.

Logic (code):

return ClientCall::leftJoin('clients', function($query) use ($salonID){
        $query->on('client_calls.phone', 'clients.phone');

        $query->join('salon_clients', function($query) use ($salonID){
            $query->on('clients.id', 'salon_clients.client_id');
            $query->where('salon_clients.salon_id', $salonID);
        });
    })
    ->where('client_calls.salon_id', $salonID)
    ->orderBy('client_calls.created_at', 'DESC')
    ->select(
        'client_calls.*',
        'clients.name'
    )
    ->get();

Return:

[
    {
        "id": 5,
        "salon_id": 1,
        "phone": "0746707241",
        "type": "missed",
        "created_at": "2018-09-02 10:36:45",
        "updated_at": "2018-09-02 10:39:32",
        "name": 'Test client',
    }
]

The problem is that the Test client, does't belong to salon_id 1 (Name should be NULL in this case). So, the innerjoin + where condition is simply passed over.

Am I missing something? Thanks again for your help!

via Chebli Mohamed