I am trying to sort a table that has parent- and child-rows. Sorting should always be performed based on the parent rows, but the childrows should always immediately follow the parent. Table data is in format of
[
{name: 'xxx', group: 'A', type: 'parent'},
{name: 'yyy', group: 'B', type: 'parent'},
{name: 'zzz', group: 'A', type: 'child'},
{name: 'qqq', group: 'A', type: 'child'}
]
So, sorted by name the correct order would be xxx,qqq,zzz,yyy.
The data comes from a Laravel/Eloquent ajax query and is displayed in a datatables table, so sorting it either client or server side would be fine.
Not related to multisort (comment below)
Couldn't figure out how to do it in SQL, DataTables callbacks/sorters (although a plugin would be an workable option), Eloquent callbacks or Yajra DataTables adapter options. So I just went the brute force way.
look up each child lines' master record for the correct sorting index and create a new indexing column for the "true" order
$dataTable_column_map = $claimPortfolio->getColumnMap();
$claimPortfolioLines = $claimPortfolio->lines()->orderBy($dataTable_column_map[$request->get('order')[0]['column']]['name'], $request->get('order')[0]['dir'])->get();
$claimPortfolioLines = ClaimPortfolioService::orderGuarantees($claimPortfolioLines);
$claimPortfolioLines = ClaimPortfolioService::filterLines($claimPortfolioLines, Input::get('search.value'));
Session::put('claimPortfolioLineIdList', $claimPortfolioLines->lists('id')->toArray());
return Datatables::of($claimPortfolioLines)
->order(function() {
return true; // cancel built-in ordering & filtering
})
->filter(function() {
return true;
})
->make(true);
public static function orderGuarantees(Collection $claimPortfolioLines)
{
$claimPortfolioLinesGuarantees = $claimPortfolioLines->filter(function ($claimPortfolioLine) {
return $claimPortfolioLine->line_type == 'GUARANTEE';
});
$claimPortfolioLines = $claimPortfolioLines->filter(function ($claimPortfolioLine) {
return $claimPortfolioLine->line_type == 'DEBT';
});
foreach ($claimPortfolioLines as $idx_line => $claimPortfolioLine)
{
$claimPortfolioLine->sortOrder = $idx_line;
foreach ($claimPortfolioLinesGuarantees as $idx_guaranteeLine => $claimPortfolioLineGuarantee)
{
if ($claimPortfolioLineGuarantee->contract_no == $claimPortfolioLine->contract_no && $claimPortfolioLine->line_type == 'DEBT')
{
$claimPortfolioLineGuarantee->sortOrder = "{$idx_line}.{$idx_guaranteeLine}";
$claimPortfolioLines->push($claimPortfolioLineGuarantee);
}
}
}
$claimPortfolioLines = $claimPortfolioLines->sortBy('sortOrder');
return $claimPortfolioLines;
}
Use orderFixed option to always apply ordering to a certain column before/after any other columns.
For example:
var table = $('#example').DataTable({
ajax: {
url:'https://api.myjson.com/bins/4n6ey',
dataSrc: ''
},
orderFixed: {
pre: [[1, 'asc'], [2, 'desc']]
},
columns: [
{ data: 'name' },
{ data: 'group' },
{ data: 'type'}
]
});
See this jsFiddle for code and demonstration.
The general solution for this in SQL is to self join and order by multiple columns, including whether it's a parent. In OP's case, assuming a table of data d (t an alias meaning table, and s meaning sort):
SELECT t.*
FROM d AS t
INNER JOIN d AS s
ON s.group = t.group
AND t.type = 'parent'
ORDER BY s.name, t.type = 'parent' DESC, t.name