Parent-child sorting

Question

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)

Possible duplicate of [PHP sort array by two field values](http://stackoverflow.com/questions/4582649/php-sort-array-by-two-field-values) — Henders, May 17 '16 at 11:34
Henders: This is not an array_multisort scenario. array_multisort sorts by multiple dimensions, in order. the same can be achieved by mysql order by or default datatables behaviour. I need a simple one-dimensional sort, except certain rows need to be clumped together (ie children of a group following the parent) regardless of the logical sorting order. sorting by group,type would result in correct behaviour, but only if sorting by group is the desired result. sorting by any other column is the issue here. — deedoo, May 17 '16 at 13:02
Ruben: I have not been able to come up with any reasonably working code thus far. PHP, Laravel, DataTables and the connecting adapter (Yajra\Datatables) all have user sort options with callbacks, but I would need to reference items outside the comparison scope (comparing items 2 and 3 by name in my example is impossible without taking the name of item 1 into account). — deedoo, May 17 '16 at 13:07

score 0 · Answer 1 · answered May 17 '16 at 15:51

0

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.

answered May 17 '16 at 15:51

Gyrocode.com

57,606
14
150
185

Not quite. This locks the parent order in place and only sorts the child rows, eg group A is always before B, which is not the correct order for reverse name sort (y,x,z,q). In reality, I dont really care about the child order, parent order is key. – deedoo May 17 '16 at 20:09

score 0 · Accepted Answer · answered Jun 01 '16 at 16:22

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.

sort by intended column using sql
separate master/child (debt/guarantee) lines

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

score 0 · Answer 3 · answered Jun 25 '19 at 03:27

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

Parent-child sorting

3 Answers3