Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

RafaelMunoznl's avatar

"Joint" tables and get All even if it does not meet the conditions

I have this query:

Appointment::join('employees', 'employees.id', 'appointments.employee_id')
            ->join('users', 'users.id', 'employees.user_id')
            ->join('offers', 'offers.id', 'appointments.offer_id')
            ->join('clients', 'clients.id', 'appointments.client_id')
            ->where('appointments.company_id', $companyId)
            ->whereDate('startDateTime', 'like', '%' . $datum . '%')
            ->orderBy('appointments.employee_id')
            ->orderBy('appointments.startDateTime')
            ->select(
                'appointments.*',
                'offers.title',
                'clients.lastname',
                'users.lastname',
                'employees.*'
            )
            ->get()
            ->groupBy('employee_id');

It gives me back the appointment per day, grouped per employee, like this:

Collection {#3158 ▼
  #items: array:7 [▼
    4 => Collection {#3163 ▼
      #items: array:5 [▼
        0 => Appointment {#1108 ▶}
        1 => Appointment {#1107 ▶}
        2 => Appointment {#1053 ▶}
        3 => Appointment {#1109 ▶}
        4 => Appointment {#1111 ▶}
      ]
    }
    5 => Collection {#3200 ▶}
    6 => Collection {#3599 ▶}
    7 => Collection {#3135 ▶}
    8 => Collection {#3162 ▶}
    9 => Collection {#3133 ▶}
    11 => Collection {#3157 ▶}
  ]
}

Is fine, but the list have just 7 employees and the company has eight employees. The employee with id = 8 does not have any appointment in that given day (sick, holidays, whatever).

So, If one employee does not have appointments, it wont be shown in tis results. In this case the employee with id "8" is not in the collection.

This is not a bug because the JOIN joints the columns that meet that condition, but...

How could I make happens that all employees get in the collection? Even if they do not have appointments. If the employee does not have appointment I want to get something like:

...
8 => Collection {#3163 ▼
      #items: array:0 [▼
        0 => []
      ]
    }

I have tried, but I do not know how to get it

0 likes
3 replies
bobbybouwmann's avatar

Well, in this case, this is never going to work. You're quering from appointment, so the only way to find employees is if they actually have an appointment. There is no way for MySQL to determine what other employees there are. Instead, you should query from another place like the employee.

So in your case, you query all employees with appointments and so on

Employee::with(['appointments' => function ($query) use ($companyId) {
    $query->where('company_id' , $companyId);
})->get();

Laravel relations can really help you out here ;)

Documentation: https://laravel.com/docs/master/eloquent-relationships#eager-loading

Please or to participate in this conversation.