How to add row number or Serial no in laravel datatable

Question

This is the bill_info table, for which i need to serialized row no like 1 2 . . . . . . . . . . . . .n

There is data list returned, how I can get serial_no custom field in datatable list view.

    $data = BillInfo::get(['bill_info.*']);

    return Datatables::of($data)
                    ->removeColumn('id')
                    ->make(true);

Chela

I dont see any serial_no field in your database. use the id. or do you want the invoice_no? – Devise

serial_no field not exist in database but it will represent the record no of the given list. There is 8 records available, so serial no will start from 1 to 8 – Chela

why dont you use the id field? or just count it inside a loop and print each iteration. – Devise

Please explain further your question, i believe it can be solved quickly and nicely. – Devise

id field value started from 168 to 175 for those of record but i need from 1 to 8 for the given record – Chela

Answer 1

Set the variable rownum at the beginning of your query. Then set the increment process in your query.

DB::statement(DB::raw('set @rownum=0'));

$data = BillInfo::get(['bill_info.*', 
                    DB::raw('@rownum  := @rownum  + 1 AS rownum')]);

return Datatables::of($data)
                ->removeColumn('id')
                ->make(true);

Here you can get rownum as serial no of the given records [1 . . . 8].

Bozen

I am using order by in my query but this does not work with order by for example if I have 3 records then it is giving 4,5,6 as rownum and if i will remove order by then it is giving 1,2,3 – Bettinabettine

Answer 2

If you are using yajra laravel datatables

Just add ->addIndexColumn()

return DataTables::of($data)
            ->addIndexColumn()
            ->make(true);

In your javascript, you can set the first row as a serial number like this

columns: [
            { data: 'DT_RowIndex', name: 'DT_RowIndex', orderable: false, searchable: false },
            { data: 'name', name: 'name' },
            { data: 'action', name: 'action' }
        ]

use DT_Row_Index instead of DT_RowIndex for older yajra datatable version

Om

In javascript, use DT_RowIndex instead of DT_Row_Index – Giorgia

in yajra v.9 this will work properly if we add orderable & searchable as false ``` { data: 'DT_RowIndex', name: 'DT_RowIndex' , orderable: false, searchable: false}, ``` – Victoir

Answer 3

When using Yajra Laravel datatables

return DataTables::of($result)
            ->addIndexColumn()
            ->make(true);

"columns": [
                {
                    "data": 'DT_RowIndex',
                    orderable: false, 
                    searchable: false
                },
]

Cule

Answer 4

In Laravel Yajra Datatables v9.x as Service Implementation, I add this in getColumns function and works well in sorting, searching, and page changing.

'id' => ['title' => 'N.', 'orderable' => false, 'searchable' => false, 'render' => function() {
            return 'function(data,type,fullData,meta){return meta.settings._iDisplayStart+meta.row+1;}';
        }],

Postmeridian

Answer 5

You can see my code :

<table id="datatable" class="table table-bordered table-striped">
                            <thead>
                            <tr>
                                <th>Sl No</th>
                                <th>Invoice No</th>
                                <th>Customer</th>
                                <th>Total Price</th>
                                <th>Paid</th>
                                <th>Due</th>
                                <th>Discount</th>
                                <th>Action</th>
                            </tr>
                            </thead>
                            <tbody>

                            </tbody>
                        </table>

And the script part:

<script type="text/javascript">
        $(function() {
            var i = 1;
            var table = $('#datatable').DataTable({
                processing: true,
                serverSide: true,
                ajax: "{{ route('collections') }}",
                columns: [{
                    "render": function() {
                        return i++;
                    }
                },
                    {
                        data: 'issue_no',
                        name: 'issue_no'
                    },
                    {
                        data: 'customer_name',
                        name: 'customer_name'
                    },
                    {
                        data: 'total_order_price',
                        name: 'total_order_price'
                    },
                    {
                        data: 'paid_amount',
                        name: 'paid_amount'
                    },
                    {
                        data: 'due_amount',
                        name: 'due_amount'
                    },
                    {
                        data: 'discount_amount',
                        name: 'discount_amount'
                    },
                    {
                        data: 'action',
                        name: 'action',
                        orderable: false,
                        searchable: false
                    }
                ],
                createdRow: function ( row, data, index ) {
                    if (data['due_amount'] > 0) {
                        $('td', row).eq(4).css('background-color', '#f4511e','color','#ffffff');
                        $('td', row).eq(4).css('color','#ffffff');
                    } else {

                    }
                    $('td', row).eq(3).addClass('text-right');
                    $('td', row).eq(4).addClass('text-right');
                    $('td', row).eq(5).addClass('text-right');
                    $('td', row).eq(6).addClass('text-right');
                }
            });

        });
    </script>

In the rendering part, you can add the auto-increment field

{
  "render": function() {
      return i++;
     }

},

Major

Answer 6

simple using render function script on yajra datatable, this is my code:

protected function getColumns()
{
    return [
            Column::make('row_number')
                    ->title('#')
                    ->render('meta.row + meta.settings._iDisplayStart + 1;')
                    ->width(50)
                    ->orderable(false),
           ];
}

Dragonet

Answer 7

Best way to give serial number in yajra datatable in laravel is below use $start in dataTable function in DataTable class

public function dataTable(QueryBuilder $query): EloquentDataTable
    {
        $start = 1;
        return (new EloquentDataTable($query))
            ->editColumn('SNo', function($row) use(&$start){
                return $start++;
            })....
    }

Acetylate