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
– Tschitsch, Commented Mar 25, 2021 at 9:19
Perhaps this is because of the big red notification at the top of DataTables manual? 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. — Daniel Protopopov
– Daniel Protopopov, Commented Mar 30, 2021 at 11:52

draw134 · Accepted Answer · 2021-03-31 18:59:52Z

6

+50

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, 2021 at 18:59

draw134

1,2194 gold badges44 silver badges100 bronze badges

answered Mar 31, 2021 at 6:40

Huzaifa Qidwai

2391 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

draw134 Over a year ago

what does this mean?

Huzaifa Qidwai Over a year ago

It will sort column by that attribute value

draw134 Over a year ago

its still the same sir

Huzaifa Qidwai Over a year ago

inspect the row and check whether you are getting count values in data-order attribute

draw134 Over a year ago

yes im getting count values but it does not sort. can you help me further in this problem?

|

Muhammad Dyas Yaskur · Accepted Answer · 2021-03-25 09:03:02Z

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, 2021 at 9:03

answered Mar 25, 2021 at 7:59

Muhammad Dyas Yaskur

8,35811 gold badges62 silver badges91 bronze badges

9 Comments

draw134 Over a year ago

So i can now sort the badges by using this? Not the id?

Muhammad Dyas Yaskur Over a year ago

sort based on the column, in your case overall_returns_count

Muhammad Dyas Yaskur Over a year ago

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.

draw134 Over a year ago

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

Muhammad Dyas Yaskur Over a year ago

Oh I forgot the last thing, add it on the rawColumns, ->rawColumns(['overall_returns_count'])

|

Collectives™ on Stack Overflow

Laravel Datatables: How to sort column with the second data

2 Answers 2

6 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related