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);

Answer 1

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

Answer 2

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++;
     }

},

Answer 3

When using Yajra Laravel datatables

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

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

Answer 4

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].