Laravel yajra Datatable : How to send parameters to DataTable Service Class for custom query?

Question

How do I pass parameters like ( res_id, resname etc ) to the DataTable Service Class (RestaurantDataTable) for Custom DB queries like ( get only restaurant where id = x, or resname = xxx etc)?

RestaurantController:

use App\DataTables\restaurantDataTable;

    class restaurantController extends AppBaseController
    {
       public function index(restaurantDataTable $restaurantDataTable)
       {

           return $restaurantDataTable->render('restaurant.index');

       }
    }

restaurantDataTable:

class restaurantDataTable extends DataTable
{

    /**
     * @return \Illuminate\Http\JsonResponse
     */
    public function ajax()
    {

        return $this->datatables
            ->eloquent($this->query())
            ->addColumn('action', 'restaurant.datatables_actions')
            ->make(true);
    }

    /**
     * Get the query object to be processed by datatables.
     *
     * @return \Illuminate\Database\Query\Builder|\Illuminate\Database\Eloquent\Builder
     */
    public function query()
    {

        $restaurants = restaurant::query();

        return $this->applyScopes($restaurants);



    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\Datatables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
            ->columns($this->getColumns())
            ->addAction(['width' => '10%'])
            ->ajax('')
            ->parameters([
                'dom' => 'Blfrtip',
                'scrollX' => false,
                'buttons' => [
                    'print',
                    'reset',
                    'reload',
                    [
                         'extend'  => 'collection',
                         'text'    => '<i class="fa fa-download"></i> Export',
                         'buttons' => [
                             'csv',
                             'excel',
                             'pdf',
                         ],
                    ],
                    'colvis'
                ]
            ]);
    }

    /**
     * Get columns.
     *
     * @return array
     */
    private function getColumns()
    {
        return [
            'res_name' => ['name' => 'res_name', 'data' => 'res_name'],
            'res_address' => ['name' => 'res_address', 'data' => 'res_address'],
            'res_state' => ['name' => 'res_state', 'data' => 'res_state'],
            'res_location' => ['name' => 'res_location', 'data' => 'res_location'],
            'res_area' => ['name' => 'res_area', 'data' => 'res_area']
        ];
    }

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

Table.blade.php:

@section('css')
    @include('layouts.datatables_css')
@endsection

{!! $dataTable->table(['width' => '100%']) !!}


@section('scripts')
    @include('layouts.datatables_js')
    {!! $dataTable->scripts() !!}
@endsection

Do I need to customize the functions in UsersDataTable Class like public function custom_query($param) {} or public function custom_ajax($param) {}?

Answer 1

Pass parameters to your function index@restaurantController (always you do) and datatable object will catch them. You can check if parameters were sent:

public function index(restaurantDataTable $restaurantDataTable)
   {
       dd($restaurantDataTable->->request()->all());
       return $restaurantDataTable->render('restaurant.index');

   }

After sending parameters, edit html() function in restaurantDataTable like this :

public function html()
{
    $url = 'yourUrl';
    if ($this->request()->has("res_id")) {
        $url = $url."?resId=".$this->request()->get("res_id");
    }

    return $this->builder()
        ->columns($this->getColumns())
        ->addAction(['width' => '10%'])
        ->ajax($url)
        ->parameters([
            'dom' => 'Blfrtip',
            'scrollX' => false,
            'buttons' => [
                'print',
                'reset',
                'reload',
                [
                     'extend'  => 'collection',
                     'text'    => '<i class="fa fa-download"></i> Export',
                     'buttons' => [
                         'csv',
                         'excel',
                         'pdf',
                     ],
                ],
                'colvis'
            ]
        ]);
}

Now datatable will send the parameters. After that you can filter the query like this:

public function ajax()
{
    return $this->datatables
        ->eloquent($this->query())
        ->addColumn('action', 'restaurant.datatables_actions')
         ->filter(function ($query) {           
            if ($this->request()->has("resId")) {
                $query->where("res_id", $this->request()->get("resId"));
            }
        })
        ->make(true);
}

I know it is not the best solution but works for now. I hope it is helpful.