Column 'department_id' in where clause is ambiguous

Question

I got error on my page like the title above. I am trying to export an Excel with the Laravel Excel extension.

Here is my code:

public function query()
    {
        $test =  Leave::query()
                ->join('departments as dep', 'leaves.department_id', '=', 'dep.id')
                ->join('employees as emp', 'leaves.employee_id', '=', 'emp.id')
                ->join('users as emplUser', 'emp.user_id', '=', 'emplUser.id')
                ->join('users as apprUser', 'leaves.approved_by_id', '=', 'apprUser.id')
                ->select('leaves.id',
                        'dep.name',
                        'emplUser.first_name',
                        'leaves.start',
                        'leaves.end',
                        'leaves.type',
                        'leaves.reason',
                        'leaves.approved',
                        'leaves.approved_on',
                        'apprUser.first_name',
                        'leaves.approved_comment',
                        'leaves.created_at',
                        'leaves.updated_at',
                        )
                ->whereDate('leaves.start','>=', $this->periodStart)
                ->whereDate('leaves.end', '<=', $this->periodEnd);
        return $test;
    }

and here is the SQL from the error message:

select
  `leaves`.`id`,
  `dep`.`name`,
  `emplUser`.`first_name`,
  `leaves`.`start`,
  `leaves`.`end`,
  `leaves`.`type`,
  `leaves`.`reason`,
  `leaves`.`approved`,
  `leaves`.`approved_on`,
  `apprUser`.`first_name`, 
  `leaves`.`approved_comment`,
  `leaves`.`created_at`,
  `leaves`.`updated_at`
from `leaves` 
  inner join `departments` as `dep` on `leaves`.`department_id` = `dep`.`id`
  inner join `employees` as `emp` on `leaves`.`employee_id` = `emp`.`id`
  inner join `users` as `emplUser` on `emp`.`user_id` = `emplUser`.`id`
  inner join `users` as `apprUser` on `leaves`.`approved_by_id` = `apprUser`.`id`
where date(`leaves`.`start`) >= 2021-07-04 and date(`leaves`.`end`) <= 2021-12-31
  and (`department_id` = 2 or `department_id` is null)
  order by `leaves`.`id` asc limit 1000 offset 0

I have notice that it says:

where ... and (`department_id` = 2 or `department_id` is null) But I have never specified department_id, just like the start and end date. I think it needs like leaves.department_id, but how can I do that when I have never write it from the first time?

Update with more code:

This is from the LeaveController:

    public function export() 
    {
        $now = Carbon::now()->startOfWeek(Carbon::SUNDAY);
        $start = $now;
        $end = $now->copy()->endOfYear();
        
        $period = new Period($start, $end);
        return (new LeavesExport)->forPeriod($period->start, $period->end)->download('download.xlsx');
    }

This is some of the code from Leave, that I found that contains department in some way:

use App\Traits\HasDepartment;

* App\Leave
 * @property int $department_id
 * @property-read \App\Department $department
 * @method static \Illuminate\Database\Eloquent\Builder|\App\Leave whereDepartmentId( $value )

class Leave extends Model
{
    use HasDepartment, ...

public static function getTypes()
   {
        try {
            return LeaveType::where('department_id', current_department()->id)->pluck('name', 'id');
        } catch (\Exception $e) {
            error_log('User id: ' . auth()->user()->id . ' does not have an assigned Department');
            return collect([]);
        }
    }
 }

Answer 1

The error in your WHERE clause is ambiguous means that the system is not able to indentify department_id because there are more than 1 column with that name. You need to specify it first.

return LeaveType::where('leaves.department_id', current_department()->id)->pluck('name', 'id');