How to query field created by addSelect in Laravel?

Question

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.

Your every tables have 'submission_date' fields? In that case, set table name. for example, $query->whereBetween('applications.submission_date', [$date_from, $date_to]) — bluestar0505, Jan 27 '23 at 13:20
try to $query->whereBetween('submission_date.created_at', [$date_from, $date_to]); — bluestar0505, Jan 27 '23 at 13:33
Which *database table* has the column `submission_date`? Is it from `customers`, `departments`, or `applications` table? Please, clarify. — steven7mwesigwa, Jan 27 '23 at 14:51

Danilo Pinotti · Answer 1 · 2023-01-27T19:14:55.650

0

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.

https://stackoverflow.com/a/67580272/6901915

edited Jan 27 '23 at 19:14

answered Jan 27 '23 at 19:12

Danilo Pinotti

18
4

score 0 · Answer 2 · answered Jan 29 '23 at 18:36

You do not create a field using addSelect. You only add a field to your query, so you cannot query it. Let's consider an example:

create table abc(id int primary key);

is a table I created as a proof-of-concept. Now, let's fill in some data:

insert into abc(id)
values(1),(2);

Now, let's query it, adding a field to it, called foo:

select id, 2 * id as foo
from abc;

And, now let's filter by foo:

select id, 2 * id as foo
from abc
where foo = 2;

We get an error, see below:

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.

How to query field created by addSelect in Laravel?

2 Answers2