How to filter Laravel 9 Daily Reports table by Team ID in User Table-CodePudding

I have a 'user' table in which there is a column 'team_id'. I also have a 'daily_report' table that includes a 'user_id' column. I am trying to filter the data in the 'daily_report' table by 'team_id'. I am using Laravel 9.

My current code in the index controller is as follows:

    public function index()
    {
        $daily_reports = DailyReport::with('user.team')->with('updated_by_user_id')
            ->when(request()->q, function ($daily_reports) {
                $daily_reports = $daily_reports->where('description', 'like', '%' . request()->q . '%');
            })->when(request()->status, function ($daily_reports) {
                $daily_reports = $daily_reports->where('status', request()->status);
            })->when(request()->user_id, function ($daily_reports) {
                $daily_reports = $daily_reports->where('user_id', request()->user_id);
            })->when(request()->team_id, function ($daily_reports) {
                $daily_reports = $daily_reports->where('user.team_id', request()->team_id);
            })->latest()->paginate(5);

        $users = User::all();
        $teams = Team::all();

        return Inertia::render('Apps/DailyReports/Index', [
            'daily_reports' => $daily_reports,
            'users' => $users,
            'teams' => $teams,
        ]);
    }

->when(request()->team_id, function ($daily_reports) { 
   $daily_reports = $daily_reports->where('user.team_id', request()->team_id);
})->latest()->paginate(5);

However, I am encountering the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user.team_id' in 'where clause'
select count(*) as aggregate from `daily_reports` where `user`.`team_id` = 2

Could you please help me resolve this issue?

CodePudding user response：

user is relationship, so have to use whereHas.

->when(request()->team_id, function ($daily_reports) {
    $daily_reports = $daily_reports->whereHas('user', function ($query) {
        $query->where('team_id', request()->team_id);
    });
})->latest()->paginate(5);

CodePudding user response：

Fixed with:

$daily_reports->whereHas('user', fn ($q) => $q->where('team_id', request()->team_id));