i want to get data for the employee with it's department and the attendance

i have three tables in my mysql database (employees,departments,attendances) and there's one to many from department to employee and one to many from employee to attendance so i'm try make search filter with department name and date in order to get the presents employees in specific day and the dismissal employees in the sameday however i don't know what i can do to get the attendance with department to make the search

MohamedTammam

2 years ago

Level 51

$day = '1-1-2024';
$departments  = Department::whereHas(
	'employees', 
	fn($employeeQuery) =>  $employeeQuery->whereHas('attendance', fn($attendanceQuery) => $attendanceQuery->where('day', $day))
);

That will get only the departments with employee that has attendance in that day.

If you want to search for the employee in specific department. You can modify the query to be

$day = '1-1-2024';
$depratment = Department::find(1);

$employees = Department::employees()->whereHas('attendance', fn($attendanceQuery) => $attendanceQuery->where('day', $day));

melsaharty

2 years ago

Level 1

@MohamedTammam thank you but what's the variable $attendaneQuery ?

MohamedTammam

2 years ago

Level 51

@melsaharty Just random name. The callback will get one parameter which is the query for the corresponding table.

$attendaneQuery refers to the query that will be implemented on attendances table.

Please or to participate in this conversation.