I am using server side processing (for large amount of dataset) with laravel 5.4 and yajra datatable but unable to perform sorting for column that is added into the table view.
My table schema as follows:
stocks table : id (pk), name, symbol
e.g.
123, apple, aapl
stockfa table: id, stock_id (fk), roe,date
e.g.
1, 123, 15, 01/01/2007
2, 123, 16, 01/01/2008
...
13, 123, 17, 01/01/2019
I wanted to have a datatable (with sorting) as follows:
id, name (sorting), symbol, latest_roe (sorting)
My laravel controller code as follows:
$stocks = Stock::whereIn('id', $idListArry)->select('stocks.*')
->addSelect(
DB::raw('(SELECT stockfa.roe FROM stockfa, stocks WHERE stocks.id = stockfa.stock_id and stockfa.date = (SELECT MAX(date) FROM stockfa where stocks.id=stockfa.stock_id)) as latestroe')
);
$stockListDatatable = Datatables::of($stocks)
->setTotalRecords($stocks->count())
->addColumn('latestroe', function ($stock) {
return round($stock->latestroe,1);
})
...
->make(true);
return $stockListDatatable;
My view blade javascript as follows:
$(function() {
$('#stocks-table').DataTable({
pageLength: 50,
order: [ 0, 'asc' ],
responsive : true,
processing: true,
serverSide: true,
....
columns: [
{ data: 'id', name: 'id',orderable: false },
{ data: 'name', name: 'name',orderable: true },
{ data: 'symbol', name: 'symbol',orderable: true },
{ data: 'latestroe',name: 'latestroe',orderable: true,sortable : true },
],
If i do not have the additional "latest_roe" column, everything works fine. But when i add the latest_roe extra column , the number of records returned is correct but the table give "No matching records found". I am running out of idea what could be the issue ?
