Laravel Datatables: How to sort column with the second data

Question

How do I sort according to the second data? The sorting works only on the ID. But in my table, I added a badge count and that's what I want to sort by.

Here is my controller

    return DataTables::of($users)
        ->addIndexColumn()
        ->addColumn('Inventories', function ($row) {
            if($row->status != 'Dumped2') {
                return '<a href="'.route('admin.clients.show-client-inventories2', $row->id).'" class="btn btn-info btn-sm">'.__("Inventories").' <span class="right badge badge-success" title="Total Lost">'.(abs($row->inv_total_lost_qty) ?? 'N/A').'</span> </a>';
            }
        })
        ->addColumn('Returns', function ($row) {
            if($row->status != 'Dumped2') {
                return ' <a href="'.route('admin.clients.show-client-returns', $row->id) .'" class="btn btn-info btn-sm">'.__('Returns').' <span class="right badge badge-warning" title="Total Returns">'.(abs($row->overall_returns_count) ?? 'N/A').'</span> </a>';
            }
            $row->status->orderBy(abs($row->overall_returns_count));
        })
        ->addColumn('inbound_shipments', function ($row) {
            if($row->status != 'Dumped2') {
                return '<a href="'. route('admin.clients.show-client-inbound-shipments', $row->id).'" class="btn btn-info btn-sm">'. __('Inbound Shipments').'<span class="right badge badge-danger" title="Total Overcharged Fees">'. abs($row->shipment_quantity_diff).'</span> </a>';
            }
        })
        ->addColumn('overcharged', function ($row) {
            if($row->status != 'Dumped2') {
                return '<a href="'. route('admin.clients.show-client-overcharged-fee', $row->id).'" class="btn btn-info btn-sm">'.__('Overcharged Fee') .' <span class="right badge badge-primary" title="Total Overcharged Fees">'.(abs($row->overall_overcharged_fees_count) ?? 'N/A').'</span> </a>';
            }
        })
        ->addColumn('ALL', function ($row) {
            if($row->status != 'Dumped2') {
                $arr = array(abs($row->overall_overcharged_fees_count), abs($row->overall_returns_count), abs($row->inv_total_lost_qty),abs($row->shipment_quantity_diff));
                return array_sum($arr);
            }
        })
        ->addColumn('credentials', function ($row) {
            if($row->status != 'Dumped2') {
                return '<button onclick="set_credentials(this)" class="btn btn-danger btn-sm"> '.__("Set Credentials").'</button>';
            }
        })
        ->addColumn('reim', function ($row) {
            if($row->status != 'Dumped2') {
                return '<a href="'. route('admin.clients.show-client-reimbursements', $row->id).'" class="btn btn-danger btn-sm">'.__('Reimbursements') .'</a>';
            }
          
        })
        ->rawColumns(['Inventories','action','Returns','inbound_shipments','overcharged','ALL','credentials','reim'])
        ->make(true);
}

return view('admin.clients.index', compact('users'));

The column I want to sort is not $row->id, what I want is the second one like abs($row->overall_returns_count).

And in my Blade code. I wrote something like this:

//Update start
{width: "10%", data: 'ALL', name: 'ALL', orderable: true, searchable: false},
{width: "10%", data: 'Inventories', name: 'Inventories', orderable: true, searchable: false},
{width: "10%", data: 'Returns', name: 'Returns', orderable: true, searchable: false},
{width: "15%", data: 'inbound_shipments', name: 'Inbound Shipments', orderable: true, searchable: false},
{width: "10%", data: 'overcharged', name: 'Overchared Fee', orderable: true, searchable: false},

As you can see, if I click the header, it doesn't sort the badge. Instead, it sorts the id as you can see in the lower left corner when I hovered the button. (id starting in 2)

System details Operating System Windows 10

PHP Version 7.2

Laravel Version 6.0

Laravel-Datatables Version ^9.6

Have you already considered, that your named route might be wrong? — Tschitsch, Mar 25 '21 at 09:19
Perhaps this is because of the big red notification at the top of [DataTables manual](https://yajrabox.com/docs/laravel-datatables/master/add-column)? ``Added columns are assumed to be computed columns and not part of the database. Thus, search/sort will be disabled on those columns. If you need them, use the editColumn api instead.`` It might be easier if you use the official documentation' [example of sorting by multiple columns](https://yajrabox.com/docs/laravel-datatables/master/order-columns). — Daniel Protopopov, Mar 30 '21 at 11:52

score 6 · Accepted Answer · edited Mar 31 '21 at 18:59

6

Try adding data-order attribute in row as below

if($row->status != 'Dumped2') {
    return ' <a href="'.route('admin.clients.show-client-returns', $row->id) .'" class="btn btn-info btn-sm">'.__('Returns').' <span class="right badge badge-warning" data-order = '.abs($row->overall_returns_count) ?? '0'.' title="Total Returns">'.(abs($row->overall_returns_count) ?? 'N/A').'</span> </a>';
  }

edited Mar 31 '21 at 18:59

draw134

1,053
4
35
84

answered Mar 31 '21 at 06:40

Huzaifa Qidwai

239
1
3

what does this mean? – draw134 Mar 31 '21 at 07:31
It will sort column by that attribute value – Huzaifa Qidwai Mar 31 '21 at 08:32
its still the same sir – draw134 Mar 31 '21 at 09:40
inspect the row and check whether you are getting count values in data-order attribute – Huzaifa Qidwai Mar 31 '21 at 09:46
yes im getting count values but it does not sort. can you help me further in this problem? – draw134 Mar 31 '21 at 09:48
i think I got it now. THanks for the help. data-order is the answer – draw134 Mar 31 '21 at 09:52

Muhammad Dyas Yaskur · Answer 2 · 2021-03-25T09:03:02.140

3

What is your data source? if your data source is laravel collection, you should render on the JavaScript when init datatable using render: function (data, action, row). Should be like:

{
    width: "10%", data: 'overall_returns_count', name: 'overall_returns_count', orderable: true, searchable: false,
    render: function (data, action, row) {
        if (row.status != 'Dumped2') {
            return ' <a href="/admin/client/' + row.id + '" class="btn btn-info btn-sm">' +
                'Returns <span class="right badge badge-warning" title="Total Returns">' + (Math.abs(data)) +
                '</span> </a>';
        }
    },
},

If your data source is query builder, you can render using PHP, but you should use editColumn instead ofaddColumn. example:

    ->editColumn('overall_returns_count', function ($row) {
        if($row->status != 'Dumped2') {
            return ' <a href="'.route('admin.clients.show-client-returns', $row->id) .'" class="btn btn-info btn-sm">'.__('Returns').' <span class="right badge badge-warning" title="Total Returns">'.(abs($row->overall_returns_count) ?? 'N/A').'</span> </a>';
        }
        $row->status->orderBy(abs($row->overall_returns_count));
    })

then in js columns :

{width: "10%", data: 'overall_returns_count', name: 'overall_returns_count', orderable: true, searchable: false},

PHP render works only if you are using eloquent/DB query builder as data source , but JS render works on all data source including Laravel Collection and eloquent builder.

Just for your information, Yajra Datatable is support multiple data source including Collection and Query Builder. Sometime if you use Collection as data source will make slow perfomance and some feature look messy, so I recommend you using Query Builder as data source.

this will return Collection :

$users = User::withCount('overall_returns')->get();

this will return Query Builder:

$users = User::withCount('overall_returns');

edited Mar 25 '21 at 09:03

answered Mar 25 '21 at 07:59

Muhammad Dyas Yaskur

6,914
10
48
73

So i can now sort the badges by using this? Not the id? – draw134 Mar 25 '21 at 08:15
sort based on the column, in your case `overall_returns_count` – Muhammad Dyas Yaskur Mar 25 '21 at 08:15
I tested and both options(JS and PHP render) is worked. but the column should from select query like `select('column_name')` or `withCount`, not from custom model attribute. – Muhammad Dyas Yaskur Mar 25 '21 at 08:21
Its working but it only returns a string. the button and the badge is not showing. I followed the second option sir also it returns an html string if i modify – draw134 Mar 25 '21 at 14:07
Oh I forgot the last thing, add it on the rawColumns, `->rawColumns(['overall_returns_count'])` – Muhammad Dyas Yaskur Mar 25 '21 at 20:39
its still, it cannot sort the badge. instad it sorts the `$row->id` – draw134 Mar 26 '21 at 16:54
I think the problem is your `overall_returns_count`, what is that? from column or what? – Muhammad Dyas Yaskur Mar 27 '21 at 00:23
From column sir – draw134 Mar 27 '21 at 01:42
what I need to sort is `$row->overall_returns_count` not the `$row->id` i think its impossible? – draw134 Mar 27 '21 at 05:52

Laravel Datatables: How to sort column with the second data

2 Answers2