Laravel datatable custom column filter

Question

I am working with two tables and I am using union for that, the datatable is showing data but I am unable to filter and orderBy it because of the union,

  $invoice = Invoice::leftjoin('users as u', 'invoices.user_id', '=', 'u.id')
            ->leftjoin('cities as c', 'u.city_id', '=', 'c.id')
            ->leftjoin('banks_lists as b', 'invoices.company_bank_id', '=', 'b.id')
            ->join('invoice_statuses as is', 'invoices.status_id', '=', 'is.id')
            ->join('user_bank_infos as ubi','ubi.user_id','=','u.id')
            ->join('invoicing_cycles as ic','ic.id','=','ubi.invoicing_cycle_id')
            ->select('invoices.id as id', 'invoices.invoice_number as invoice_number', 'u.name as shipper', 'c.name as city', 'invoices.total_charges as total_charges', 'invoices.total_gst as total_gst', 'invoices.total_invoice_amount as total_invoice_amount', 'invoices.created_at as created_at', 'invoices.due_date as due_date', 'invoices.received_date as received_date', 'b.name as company_bank', 'invoices.received_amount as received_amount', 'invoices.tax_amount as tax_amount', 'invoices.deposit_date as deposit_date', 'is.name as status', 'invoices.status_id as status_id', 'invoices.invoicing_date as invoicing_date','ic.name as invoicing_cycle','invoices.invoice_type as invoice_type',DB::raw('NULL as payment_type'),DB::raw('1 as account_type'))
            ->whereIn('is.id', [1,3])
            ->where('ubi.default_bank',1);

        $invoices = InvoiceForReimbursement::join('users as u', 'invoice_for_reimbursements.user_id', '=', 'u.id')
            ->join('cities as c', 'u.city_id', '=', 'c.id')
            ->select( 'invoice_for_reimbursements.id as id','invoice_for_reimbursements.invoice_number as invoice_number', 'u.name as shipper', 'c.name as city', 'invoice_for_reimbursements.total_charges as total_charges', 'invoice_for_reimbursements.total_gst as total_gst', 'invoice_for_reimbursements.total_invoice_amount as total_invoice_amount', 'invoice_for_reimbursements.created_at as created_at',DB::raw('NULL as due_date'),DB::raw('NULL as received_date'),DB::raw('NULL as company_bank'),DB::raw('NULL as received_amount'),DB::raw('NULL as tax_amount'),DB::raw('NULL as deposit_date'),DB::raw('NULL as status'),DB::raw('NULL as status_id'), 'invoice_for_reimbursements.invoicing_date as invoicing_date',DB::raw('NULL as invoicing_cycle'),DB::raw('NULL as invoice_type'),'invoice_for_reimbursements.payment_type as payment_type',DB::raw('2 as account_type'))
            ->where('invoice_for_reimbursements.to_show',1)
            ->union($invoice); 

the column I am trying to filter is the account_type column which does not exists in either of the table

I have added the column like this in datatable

 $datatables = Datatables::of($invoices)
            ->addColumn('account', function($invoice) {
              if($invoice->account_type == 1){
                  return 'Corporate Account';
              }
              else{
                  return 'Reimbursement Account';
              }
            });

since the column doesn't exists it gives the error like account_type does not exists

so how can I filter and order by it?

any help will be appreciated

Answer 1

You can create a view in your database. Since the database RDBMS was not specified, I can only provide a vague description of it, but in MySQL the syntax looks like

create view <view_name> as <your_query>

where view_name is the name of the view you would like to use in the future and your_query is the raw query you are to use. In general, you would want to define your query in as general terms as possible (without the where clause), but in some cases, when you are operating with large tables and this causes performance issues, you might consider the creation of several views, to avoid executing filterless queries. Some RDBMS systems provide the possibility to use materialized views (Postgres, for example) that are filled and stored in which case you can create a filterless view once, it will be stored in the database and then query the stored materialized view as if it was a table.

The point is that using Eloquent you can query views as if they were tables. So, if you convert your composite query into a view or a materialized view, then your Eloquent query will only have a select clause, a from clause where you specify only your view and a where clause. From Eloquent's perspective the view would be considered to be a table and its field arising from the union would be an ordinary field.