Question 16040 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to query field created by addSelect in Laravel?

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.

Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 0

    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:

    enter image description here

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search