RafaelMunoznl
1 week ago

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

Posted 1 week ago by RafaelMunoznl

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

Please sign in or create an account to participate in this conversation.