the search selection option in yajra datatables doesn't show all pages

elmasry_n

hello Dears,
I hope some help as i using yajra datatables with laravel
and the search selection option just show the current page as i using
'lengthMenu' => [[10, 25, 50, 100, 250, -1], [10, 25, 50, 100, 250, trans('lang.All')]],
i need to show all pages

and the below is my initComplete function code

this.api().columns([4,5]).every( function () {
                        var column = this;
                        var select = $('<select><option value=\"\"></option></select>')
                            .appendTo( $(column.footer()).empty() )
                            .on( 'change', function () {
                                var val = $.fn.dataTable.util.escapeRegex(
                                    $(this).val()
                                );
         
                                column
                                    .search( val )
                                    .draw();
                                    
                                
                            } );
                            column.data().unique().sort().each( function ( d, j ) {
                            select.append( '<option value=\"'+d+'\">'+d+'</option>' )
                        } );
                    } );

kthorngren

Are you using server side processing? If so then the server script is responsible for searching. In this case you will need to get support from the developer of the server side script (yajra datatables).

Kevin

elmasry_n

Thanks kthorngren for your respond

No Iam not just I`m using UsersDataTable.php file and that's supported from yajra and the below my builder html code

public function html()
    {
        return $this->builder()
            ->setTableId('users-table')
            ->minifiedAjax()
            ->orderBy(1)
            ->columns($this->getColumns())
            ->parameters([
                'dom' => 'Blfrtip',


                'buttons' => [
                    ['create', 'print', 'excel', 'reload'],
                    ['className' => 'delBtn', 'text' => '<i class="fa fa-trash"></i> ' . trans("lang.Delete")]
                ],

                'lengthMenu' => [[10, 25, 50, 100, 250, -1], [10, 25, 50, 100, 250, trans('lang.All')]],

by the way i made filterColumn as the below

->filterColumn('roles', function ($query, $keyword) {
                if (stristr('Editor', $keyword))
                    $query->whereHas('roles', function (Builder $query) {
                        $query->where('name', 'Editor');
                    });
                elseif (stristr('User', $keyword))
                    $query->whereHas('roles', function (Builder $query) {
                        $query->where('name', 'User');
                    });
                elseif (stristr('Admin', $keyword))
                    $query->whereHas('roles', function (Builder $query) {
                        $query->where('name', 'Admin');
                    });
            })

are you recommend to ask yajra datatables support?

elmasry_n

and that is my ref js that i`m using
<script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>

kthorngren

I think maybe I misunderstood the problem. Are you having a problem with the lengthMenu or a problem with the select options for the column search?

by the way i made filterColumn as the below

When is this used? Is this search resulting on only the current page being available?

are you recommend to ask yajra datatables support?

Depends on where and what the problem is.

Can you post a link to your page or a test case replicating the issue so we can take a look?
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

elmasry_n

kindly be noted the below screen shot as Iam trying to get (Admin, Editor and User) in the search options but it show (User and Editor only).

elmasry_n

Dear Kevin thank you for your fast respond and sorry my comment may be deleted by mistake so i was tell that i cannot use live.datatables.net as i using in my blade file
{!! $dataTable->scripts() !!} and {!! $dataTable->table() !!}
and the below is my whole code for UsersDataTable.php

<?php

namespace App\DataTables;

use App\User;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Services\DataTable;

class UsersDataTable extends DataTable
{

    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query)
            ->editColumn('created_at', function ($query) {
                return Carbon::parse($query->created_at)->format('Y-m-d');
            })


            ->addColumn('roles',  function ($query) {
                return $query->roles->map(function ($role) {
                    return $role->name;
                })->implode('<br>');
            })
            ->editColumn('profile', function ($model) {
                return $model->profile->lang == "en" ? __("lang.English") : ($model->profile->lang == "ar" ? __("lang.Arabic") : ($model->profile->lang  == Null ? __("lang.Without") :
                            ''));
            })
            ->filterColumn('profile.lang', function ($query, $keyword) {
                if (stristr(__("lang.English"), $keyword))
                    $query->whereHas('profile', function (Builder $query) {
                        $query->where('lang', 'en');
                    });
                elseif (stristr(__("lang.Arabic"), $keyword))
                    $query->whereHas('profile', function (Builder $query) {
                        $query->where('lang', 'ar');
                    });
                elseif (stristr(__("lang.Without"), $keyword))
                    $query->whereHas('profile', function (Builder $query) {
                        $query->where('lang', Null);
                    });
            })
            ->filterColumn('roles', function ($query, $keyword) {
                if (stristr('Editor', $keyword))
                    $query->whereHas('roles', function (Builder $query) {
                        $query->where('name', 'Editor');
                    });
                elseif (stristr('User', $keyword))
                    $query->whereHas('roles', function (Builder $query) {
                        $query->where('name', 'User');
                    });
                elseif (stristr('Admin', $keyword))
                    $query->whereHas('roles', function (Builder $query) {
                        $query->where('name', 'Admin');
                    });
            })

            ->addColumn(
                'show',
                '<a href="{{ url("profile/".$id)}}" class="btn btn-primary btn-sm d-flex flex-nowrap"><i class="fas fa-eye mr-1 my-auto"></i> {{ __("lang.Show") }}</a>'
            )
            ->addColumn(
                'edit',
                '<a href="{{ route(adminview("users.edit"),$id)}}" class="btn btn-info btn-sm d-flex flex-nowrap"><i class="fas fa-edit mr-1 my-auto"></i> {{ __("lang.Edit") }}</a>'
            )
            ->addColumn(
                'delete',
                'admin.users_delBtn'
            )
            ->addColumn('checkbox', '<input type="checkbox" name="checked[]" class="item_checkbox" value="{{ $id }}" />')
            ->rawColumns([
                'checkbox', 'show', 'edit', 'delete', 'roles'
            ]);
    }


    public function query(User $model)
    {
        return $model->newQuery()->with('roles', 'profile')
            ->selectRaw('users.*')
    }

    public function html()
    {
        return $this->builder()
            ->setTableId('users-table')
            ->minifiedAjax()
            ->orderBy(1)
            ->columns($this->getColumns())
            ->parameters([
                'dom' => 'Blfrtip',


                'buttons' => [
                    ['create', 'print', 'excel', 'reload'],
                    ['className' => 'delBtn', 'text' => '<i class="fa fa-trash"></i> ' . trans("lang.Delete")]
                ],

                'lengthMenu' => [[10, 25, 50, 100, 250, -1], [10, 25, 50, 100, 250, trans('lang.All')]],


                'initComplete' => "function () {
                    this.api().columns([1,2,3,6]).every(function () {
                        var column = this;
                        var input = document.createElement(\"input\");
                        $(input).attr( 'style', 'text-align: center;width: 100%');
                        $(input).appendTo($(column.footer()).empty())
                        .on('keyup', function () {
                            column.search($(this).val(), false, false, true).draw();
                        });
                    });

                    
                    this.api().columns([4,5]).every( function () {
                        var column = this;
                        var select = $('<select><option value=\"\"></option></select>')
                            .appendTo( $(column.footer()).empty() )
                            .on( 'change', function () {
                                var val = $.fn.dataTable.util.escapeRegex(
                                    $(this).val()
                                );
         
                                column
                                    .search( val )
                                    .draw();
                                    
                                
                            } );
                            column.data().unique().sort().each( function ( d, j ) {
                            select.append( '<option value=\"'+d+'\">'+d+'</option>' )
                        } );
                    } );
                        


                }",

            ]);
    }


    protected function getColumns()
    {
        return [
            Column::make('checkbox')
                ->title('<input type="checkbox" class="check_all" onclick="check_all()" />')
                ->orderable(false)
                ->searchable(false)
                ->exportable(false)
                ->printable(false),


            Column::make('id')
                ->title(__('lang.User id')),
            Column::make('name')
                ->title(__('lang.Name')),
            Column::make('email')
                ->title(__('lang.Email')),
            Column::make('roles')
                ->title(__('lang.Role')),
            Column::make('profile', 'profile.lang')
                ->title(__('lang.Favorite language')),
            Column::make('created_at')
                ->title(__('lang.Created_at')),



            Column::computed('show')
                ->title(__('lang.Show'))
                ->orderable(false)
                ->searchable(false)
                ->exportable(false)
                ->printable(false),
            Column::computed('edit')
                ->title(__('lang.Edit'))
                ->orderable(false)
                ->searchable(false)
                ->exportable(false)
                ->printable(false),
            Column::computed('delete')
                ->title(__('lang.Delete'))
                ->orderable(false)
                ->searchable(false)
                ->exportable(false)
                ->printable(false),
        ];
    }


    protected function filename()
    {
        return 'Users_' . date('YmdHis');
    }
}

kthorngren

The code in your initComplete option builds the select list based on the data in that column of the table. Specifically this code:

                            column.data().unique().sort().each( function ( d, j ) {
                            select.append( '<option value=\"'+d+'\">'+d+'</option>' )

Do you have Admin in that column?

Kevin

elmasry_n

Yes I have with the page No.1 with user No.1 as the option list just get the current page not all pages. on the another side the admin will be shown if i change the order by or remove lengthMenu to show all items in 1 page.

thanks for trying helping me and i hope find good idea to solve this issue.

kthorngren

Since you are not using server side processing Datatables will contain all of the rows that are at the client. Look at the browser's network inspector tool to see how many rows are retrieved when the page loads.

Kevin

elmasry_n

The rows are retrieved is only 10 from 0: Object to 9: Object according to Show 10 entries for each page.

kthorngren

That looks like Datatables server side processing where the server script only returns the number of rows for the page. So you have only 10 rows at the client. The code to build the select will only build the selects using the data in the client.

One option is to return the select options for all the rows on the first draw (draw: 1) from the server script. You can use that to load the search options. It will require modifying your server script.

Another option is to use a non-server side processing server script so all the rows are returned to the client.

Kevin

elmasry_n

sorry Kevin i don't got it. may be the sample guy like me can't get that great explain.
could you get example for one option that you mentioned?

kthorngren

Either of those options would require changes to your server script which I think you said is provided by Yajya Datatables. You will need to contact them to learn how to change their script to support your needs.

Kevin

elmasry_n

Ok i will contact them.
Thank you Kevin

kthorngren

If all you want is to have those three search optoins you can manually create the search options for that column. Your other column searches will only contain the options available on the page.

Kevin

elmasry_n

I got what you mean but my be i will need to add some options in future so it would be better to get the options dynamically.
Thanks Kevin