jquerylaraveldatatablesyajra-datatable

How to add select dropdown filters

Summary of problem or feature request

I am trying to add 3 filters (Status, From, To). How can I do that using the Yajra\DataTables\Services\DataTable class?

Code snippet of problem

<?php

namespace App\DataTables\Payments;

use App\Models\Order;
use App\Models\Payment;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Services\DataTable;

class BankTransferDataTable extends DataTable
{
    protected $exportColumns = [
        'id', 'order_id', 'order_number', 'invoice_number', 'bulk_order_key',
        'add_on', 'email', 'mobile_number', 'g_bank', 'bank_name', 'account_name',
        'account_number', 'payment_date', 'amount', 'deposit_slip', 'status',
        'transaction_id', 'created_at', 'updated_at', 'phone', 'order_status', 'order_total'
    ];

    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query)
            // ->filter(function ($query) {
            //  if (request()->has('search')) {
            //      $query->where(function($query) {
            //          $query->where('categories.name', 'LIKE', '%' . request('search')['value'] . '%')
            //          ->orWhere('categories.slug', 'LIKE', '%' . request('search')['value'] . '%');
            //      }); 
            //  }
            // })
            ->addColumn('Order #', function ($payment) {
                return $payment->bulk_order_key;
            })
            ->addColumn('Gifted Account', function ($payment) {
                return $payment->g_bank;
            })
            ->addColumn('Bank Name', function ($payment) {
                return $payment->bank_name;
            })
            ->addColumn('Account Name', function ($payment) {
                return $payment->account_name;
            })
            ->addColumn('Status', function ($payment) {
                if ($payment->transaction_id) {
                    return $payment->get_wallet_transaction_status();
                } else if($payment->bulk_order_key) {
                    $orderStatus = Order::select(['status'])
                        ->where('bulk_order_key', $payment->bulk_order_key)
                        ->first();

                    if ($orderStatus) {
                        return $orderStatus->status;
                    }
                }

                return $payment->order_status;
            });
            // ->addColumn('Actions', function ($category) {
            //  return view('backend.catalog.categories.action', compact('category'));
            // });
    }

    /**
     * Get query source of dataTable.
     *
     * @param  \App\Payment  $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Payment $model)
    {
        $filters = request()->has('search.value') && filled(request('search.value')) ? json_decode(request('search.value'), true)['filters'] : [];

        return $model->newQuery()
            ->selectRaw('payments.*, orders.phone, orders.status as order_status, orders.total as order_total')
            ->leftJoin('orders', 'payments.order_id', '=', 'orders.id')
            ->whereRaw('payments.bulk_order_key <> ""');

            if (filled($filters['status'])) {
                $query->where('payments.status', $filters['status']);
            }

            $query->when((request()->has('from') && request()->has('to')), function ($query) {
                $query->whereDate('payments.created_at', '>=', request()->get('from'));
                $query->whereDate('payments.created_at', '<=', request()->get('to'));
            })
            ->when((request()->has('from')), function ($query) {
                $query->whereDate('payments.created_at', '>=', request()->get('from'));
                $query->whereDate('payments.created_at', '<=', date('Y-m-d', strtotime('today')));
            })
            ->when((request()->has('to')), function ($query) {
                $query->whereDate('payments.created_at', '>=', date('Y-m-d', strtotime('-5 years')));
                $query->whereDate('payments.created_at', '<=', request()->get('to'));
            })
            ->orderBy('payments.created_at', 'desc');
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
            ->setTableId('bankTransferPaymentsTable')
            ->columns($this->getColumns())
            ->minifiedAjax()
            ->dom('Brtip')
            ->orderBy(1)
            ->buttons(
                Button::make('export')->buttons(['csv']),
                Button::make('reset')
                    ->text('<span><i class="fa fa-undo"></i> Refresh</span>'),
            );
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::make('Order #'),
            Column::make('Gifted Account'),
            Column::make('Bank Name'),
            Column::make('Status'),
            // Column::make('Actions')
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'bank_transfer_payments_report_' . date('YmdHis');
    }
}

And for the view:

{{ $dataTable->scripts() }}

<script>
    $(function () {
        const filter = () => {
            const filters = {
                filters: {
                    status: $('#filterStatus').val()
                }
            };

            // window.LaravelDataTables["bankTransferPaymentsTable"].destroy();

            window.LaravelDataTables["bankTransferPaymentsTable"].search(JSON.stringify(filters)).draw();
                
            $('#bankTransferPaymentsTable').DataTable().ajax.reload();
        };

        $(document).on('change', '#filterStatus', function() {
            filter();
        });
    });
</script>

Filters screenshot

enter image description here

Issue

When selecting status from the dropdown, the table data is not refreshing/reloading. I am not also getting any output from the console.

System details

Solution

  • Seems like there is a return statement in your code preventing your code (thats after the return) to be executed:

    public function query(Payment $model)
    {
        $filters = request()->has('search.value') && filled(request('search.value')) ? json_decode(request('search.value'), true)['filters'] : [];

        return $model->newQuery() //Change this here and it should be solved :)
            ->selectRaw('payments.*, orders.phone, orders.status as order_status, orders.total as order_total')
            ->leftJoin('orders', 'payments.order_id', '=', 'orders.id')
            ->whereRaw('payments.bulk_order_key <> ""');

           //... rest of code
    }