SQLSTATE[42S22]: Column not found: 1054 Unknown column error on laravel eloquent

Question

In my laravel application I have following function.

This will list down my users in a datatable inside a vue component and users are allowed to filter the records by certain parameters.

public function execute()
    {
        return User::selectRaw('users.id as id, users.first_name as first_name, users.last_name as last_name, users.country_id as country_id, users.email as email')
            ->selectRaw('departments.name as department_name, departments.department_type_id as department_type_id')
            ->selectRaw('project_specific_job_titles.title as job_title')
            ->selectRaw('min(project_track_records.id) as project_user_id')
            ->selectRaw('COUNT(
                DISTINCT(IF(
                    certificate_project_specific_job_title.certificate_id IS NOT NULL
                    AND certificate_project_specific_job_title.mandatory = 1,
                    certificate_project_specific_job_title.certificate_id,
                    null
                ))
            ) AS available_certificate_count')
            ->selectRaw('(
                SELECT count(*) from
                    (
                        SELECT certificate_project_specific_job_title.certificate_id from certificate_project_specific_job_title
                        WHERE certificate_project_specific_job_title.project_specific_job_title_id=project_track_records.project_specific_job_title_id
                        AND certificate_project_specific_job_title.mandatory = 1
                    ) as certificate_count_sub_query
                ) as certificate_count
            ')
            ->leftJoin('certificate_user', 'certificate_user.user_id', '=', 'users.id')
            ->join('project_track_records', 'project_track_records.user_id', '=', 'users.id')
            ->leftJoin('certificate_project_specific_job_title', function ($join) {
                $join->on( 'certificate_project_specific_job_title.certificate_id', '=', 'certificate_user.certificate_id')
                    ->whereRaw('certificate_project_specific_job_title.project_specific_job_title_id=project_track_records.project_specific_job_title_id');
            })
            ->leftjoin('project_specific_job_titles', 'project_specific_job_titles.id', '=', 'project_track_records.project_specific_job_title_id')
            ->leftJoin('departments', 'departments.id', '=', 'project_specific_job_titles.department_id')
            ->whereNull('project_track_records.deleted_at')
            ->whereNotNull('project_track_records.accepted_at')
            ->where('users.first_name', '<>', '')
            ->where('users.last_name', '<>', '')
            ->groupBy('users.id','project_track_records.project_specific_job_title_id')
            ->when(request('search_text'), function ($query) {
                $query->where(function ($query) {
                    $query->whereRaw("CONCAT_WS(' ', `first_name`, `last_name`) like '%".request('search_text')."%'");
                });
            })
            ->when(request('employee_type'), function ($query) {
                $query->whereIn('users.is_subcontractor', request('employee_type'));
            })
            ->when(request('nationality'), function ($query) {
                $query->whereIn('users.country_id', request('nationality'));
            });
    }

But every time when I try to run this it gives me an sql error saying,

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'project_track_records.project_specific_job_title_id'

In my database, inside my project_track_records table I have project_specific_job_title_id column and even it contains data..

I even tried flushing my database and running migrations multiple times but this kept giving me unknown column error and I'm struggling to find the mistake I'm doing there...

My laravel version is 8.83.9 and php version is 8.1.4

Answer 1

You can do this in several ways, including this method is that you enter the xampp file, then the MySQL file, and then to the data file, in which you will find all your data bases, and you can also send a table from them only, not all the data