How to query field created by addSelect in Laravel?-CodePudding

I have the following query where I am adding three new columns by using the addSelect function

   DB::connection('mysql_slave')
        ->table('applications')
        ->whereNull('applications.deleted_at')
        ->when($column != 'contract_return_date' && $column != 'contract_delivery_date',function ($query) use ($column,$date_from,$date_to){
            return $query->whereBetween('applications.'.$column, [$date_from, $date_to]);
        })
        ->join('customers','applications.customer_id','=','customers.id')
        ->join('departments','applications.department_id','=','departments.id')
        ->select([
            'applications.id',
            'applications.customer_id',
            DB::raw('CONCAT(IFNULL(customers.last_name,"")," ",customers.first_name ) as customers_name'),
            DB::raw('CONCAT(IFNULL(applications.last_name,"")," ",applications.first_name ) as contract_name'),
            'applications.offer_type as offer_type',
            'applications.status_id',
            'applications.contract_no',
            'applications.current_provider',
            'applications.extra_offer',
            'applications.offer_warranty',
            'applications.department_id',               
            'customers.mobile_phone as customer_mobile',
            'applications.program as program',
            'applications.saled_by_text as saler',
            'departments.name as department',
            'applications.created_at as created_at',
            'applications.created_at as saled_at',
            DB::raw('IF(applications.sale=1,"NAI","OXI") as sale'),
        ])

        ->addSelect(['submission_date'=> StatusLog::select('created_at')
            ->whereColumn('application_id','applications.id')
            ->where('status','=',1)
            ->latest()
            ->take(1)
        ])

        ->addSelect(['resubmission_date'=> StatusLog::select('created_at')
            ->whereColumn('application_id','applications.id')
            ->where('status','=',2)
            ->latest()
            ->take(1)
        ])
        ->addSelect(['error_date' => StatusLog::select('created_at')
            ->whereColumn('application_id','applications.id')
            ->whereIn('status', [5, 6])
            ->latest()
            ->take(1)
        ]) ->when($column == 'contract_delivery_date',function ($query) use ($date_from,$date_to){
            return $query->whereBetween('submission_date', [$date_from, $date_to]);

        });

The above query is used to print data on a datatable.

The query includes columns that were added using the addSelect function, and these columns are being displayed correctly on the table.

However, when I try to query the submission_date field, I am encountering an error:

 1054 Unknown Column submission_date.

Is there a way to query columns that were created using the addSelect function in Laravel?

Thank you for your help and I apologize for any errors in my English.

CodePudding user response：

I think you cant do this. The custom select field (submission_date) is computed after the main query results. This is a limitation of the Database.

But you can use the HAVING operator instead.

So, if you want to add a field and query by it, then you will need to either hardcode the field's equivalent into your conditional OR create a view of the table where the field exists OR add the field to the table.