I am having a problem with searching of records in join table fields.I need all of the columns to be searched including join table column.

Here is my controller function for All State with Country:

public function allStates()
{
    $states = State::select(['id', 'country_id', 'state_type', 'state_code', 'state_name', 'status'])->orderBy('country_id','Asc');
    return Datatables::of($states)
        ->addColumn('checkes', function ($states) {

            $data = $states;
            return view('partials.datatable.table_first_column_checkbox', pact('data'))->render();
        })
        ->editColumn('country_id', function ($states) {
            return  $states->country ? $states->country->country_name : "N/A";
        })
        ->editColumn('status', function ($states) {

            $data = $states;
            $statusChangeRoute = route('state.change.status');
            return view('partials.datatable.status-switch', pact('data','statusChangeRoute'))->render();
        })
        ->addColumn('action', function ($states) {

            $editRoute = route('states.edit', $states->id);
            $viewRoute = route('states.show', $states->id);
            $controlKeyword = 'state';
            return view('partials.datatable.table_edit_delete_action', pact('editRoute','viewRoute','controlKeyword'))->render();
        })
        ->addColumn('DT_RowId', function ($states) {

            return "tr_" . $states->id;
        })
        ->rawColumns(['checkes', 'status', 'action'])
        ->make(true);
}

In this function I just edited country_id column and return $states->country->country_name

and here is my js function :

<script type="text/javascript">
    $(document).ready(function () {
        table = $('#tblState').DataTable({
            processing: true,
            serverSide: true,
            pageLength: 10,
            ajax: {
                url: "{{ route('admin.states.list') }}",
                type: "POST",
                data: {_token: "{{csrf_token()}}"}
            },
            columns: [
                {data: 'checkes',    name: 'checkes', orderable: false, searchable: false},
                {data: 'country_id', name: 'country_id'},
                {data: 'state_type', name: 'state_type'},
                {data: 'state_code', name: 'state_code'},
                {data: 'state_name', name: 'state_name'},
                {data: 'status',     name: 'status'},
                {data: 'action',     name: 'action', orderable: false, searchable: false}
            ],
            "bStateSave": true,
            initComplete: function (settings, json) {
                // called on first time initialization
            },
            drawCallback: function (settings) {
                // called on every server request
                // below function is pulsory put here with table id param
                initDTCheckBox('tblState');
            }
        });
    });
</script>

Here is my view screen

I am having a problem with searching of records in join table fields.I need all of the columns to be searched including join table column.

Here is my controller function for All State with Country:

public function allStates()
{
    $states = State::select(['id', 'country_id', 'state_type', 'state_code', 'state_name', 'status'])->orderBy('country_id','Asc');
    return Datatables::of($states)
        ->addColumn('checkes', function ($states) {

            $data = $states;
            return view('partials.datatable.table_first_column_checkbox', pact('data'))->render();
        })
        ->editColumn('country_id', function ($states) {
            return  $states->country ? $states->country->country_name : "N/A";
        })
        ->editColumn('status', function ($states) {

            $data = $states;
            $statusChangeRoute = route('state.change.status');
            return view('partials.datatable.status-switch', pact('data','statusChangeRoute'))->render();
        })
        ->addColumn('action', function ($states) {

            $editRoute = route('states.edit', $states->id);
            $viewRoute = route('states.show', $states->id);
            $controlKeyword = 'state';
            return view('partials.datatable.table_edit_delete_action', pact('editRoute','viewRoute','controlKeyword'))->render();
        })
        ->addColumn('DT_RowId', function ($states) {

            return "tr_" . $states->id;
        })
        ->rawColumns(['checkes', 'status', 'action'])
        ->make(true);
}

In this function I just edited country_id column and return $states->country->country_name

and here is my js function :

<script type="text/javascript">
    $(document).ready(function () {
        table = $('#tblState').DataTable({
            processing: true,
            serverSide: true,
            pageLength: 10,
            ajax: {
                url: "{{ route('admin.states.list') }}",
                type: "POST",
                data: {_token: "{{csrf_token()}}"}
            },
            columns: [
                {data: 'checkes',    name: 'checkes', orderable: false, searchable: false},
                {data: 'country_id', name: 'country_id'},
                {data: 'state_type', name: 'state_type'},
                {data: 'state_code', name: 'state_code'},
                {data: 'state_name', name: 'state_name'},
                {data: 'status',     name: 'status'},
                {data: 'action',     name: 'action', orderable: false, searchable: false}
            ],
            "bStateSave": true,
            initComplete: function (settings, json) {
                // called on first time initialization
            },
            drawCallback: function (settings) {
                // called on every server request
                // below function is pulsory put here with table id param
                initDTCheckBox('tblState');
            }
        });
    });
</script>

Here is my view screen

• javascript
• php
• laravel
• datatables

Share Improve this question Follow asked Sep 8, 2019 at 10:40 JavedJaved 85744 gold badges2323 silver badges4949 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 7

You can use directly in the query :

$states = State::select(['id', 'country_id', 'state_type', 
                'state_code', 'state_name', 'status'])
    ->orderBy('country_id', 'Asc')
    ->with('country');

Then directly use in your JS :

{data: 'country.country_name', name: 'country.country_name'},

addColumn just impacts the view of the column, but not the query. So when you want to order, or search in your datatable, the plugin can't find the value, you must have it in the query