Removing duplicates from laravel eloquent

Viewed 136

Inside my controller I have following function,

public function index($locale, Company $company, Department $department, Request $request)
    {
        $this->authorize('viewUsers', [Department::class, $company, $department]);

        $users = $department->usersWithSubcontractors()
            //            ->notApiUser()
            ->select(
                'users.id',
                'first_name',
                'last_name',
                'job_title',
                'email',
                'phone_number',
                'unique_id',
                'phone_country_calling_code',
                'country_id',
                'company_name',
                'is_subcontractor',
            )->addSelect([
                'current_jobtitle' => JobTitle::selectRaw('IFNULL(project_specific_job_titles.title, job_titles.title)')
                    ->join('project_track_records', 'project_track_records.user_id', 'users.id', 'inner')
                    ->join('project_specific_job_titles', 'project_track_records.project_specific_job_title_id', 'project_specific_job_titles.id', 'inner')
                    ->whereColumn('job_titles.id', 'project_specific_job_titles.job_title_id')
                    ->whereDate('project_track_records.start_date', '<=', Carbon::now())
                    ->whereDate('project_track_records.end_date', '>=', Carbon::now())
                    ->limit(1),
                'dept_user_id' => DepartmentUser::selectRaw('id')->wherecolumn('users.id','department_user.user_id')->limit(1),    
                'department_jobtitle' => JobTitle::selectRaw('title')->whereColumn('job_titles.id', 'department_user.job_title_id'),
            ])->when(request('q'), function ($q) {
                $q->where(function ($q) {
                    $q->whereRaw("CONCAT_WS(' ', `first_name`, `last_name`) like '%" . request('q') . "%'");
                });
            })->when($request->get('employeeType'), function ($q) use ($request) {
                $q->whereIn('users.is_subcontractor', $request->get('employeeType'));
            })->paginate(\request('per_page', config('repository.pagination.limit')));

        $users->load('country');
        $users->append(['profile_image']);
        $users->makeVisible(['unique_id']);

        $users->map(fn ($item) => $item->certificate_matrix = (new GetCertificationMatrixQueryAction())->execute($item->id, $department->id));

        if ($request->filled('certificateStatus')) {
            $valid = in_array('valid', $request->get('certificateStatus'));
            $expire_soon = in_array('expire soon', $request->get('certificateStatus'));
            $expired = in_array('expired', $request->get('certificateStatus'));

            if ($valid) $users->setCollection($users->filter(fn ($item) => $item->certificate_matrix->filter(fn ($certificate) => $certificate->matrix_status && !$certificate->expire_soon && !$certificate->expired)->count() === $item->certificate_matrix->count())->values());
            if ($expire_soon && !$expired) $users->setCollection($users->filter(fn ($item) => $item->certificate_matrix->filter(fn ($certificate) => $certificate->matrix_status && $certificate->expire_soon)->count() > 0)->values());
            if ($expired && !$expire_soon) $users->setCollection($users->filter(fn ($item) => $item->certificate_matrix->filter(fn ($certificate) => $certificate->matrix_status && $certificate->expired)->count() > 0)->values());
        }

        if ($request->filled('matrixStatus')) {
            $compliant = in_array('compliant', $request->get('matrixStatus'));
            $non_compliant = in_array('non-compliant', $request->get('matrixStatus'));

            if ($non_compliant && !$compliant)
                $users->setCollection($users->filter(fn ($item) => $item->certificate_matrix->filter(fn ($certificate) => $certificate->matrix_status === 0 || $certificate->expire_soon || !$certificate->expired)->count() > 0)->values());

            if ($compliant && !$non_compliant)
                $users->setCollection($users->filter(fn ($item) => $item->certificate_matrix->filter(fn ($certificate) => $certificate->matrix_status && !$certificate->expire_soon && !$certificate->expired)->count() == $item->certificate_matrix->count())->values());
        }
        return response()->json($users);
    }

Issue with this list is that, this eloquent gives me duplicate entries. How can I remove duplicates from this eloquent results list? I tried gruop the result by, dept_user_id but this gives me unknown column error....

I'm trying to remove this, as it this gives me duplicate key error on the vue end..

0 Answers
Related