Order

ZeoNish

Can I do the sorting this way?

Cols
id | user name | date

1 | User 1 | Date
2 | User 2 | Date
3 | User 3 | Date
5 | User 1 | Date
6 | User 1 | Date
7 | User 2 | Date
8 | User 1 | Date

need

User 1 First order
1 | User 1 | Date
5 | User 1 | Date
6 | User 1 | Date
8 | User 1 | Date

and asc/desc other rows
2 | User 2 | Date
3 | User 3 | Date
7 | User 2 | Date

Thx.

allan

I'm not quite following the rules for the sort applied I'm afraid. Could you elaborate a bit?

Allan

ZeoNish

all rows where collumn "user name" == User 1 upper in ordering

ZeoNish

demo my columns

id | user name | date

data rows
1 | User 1 | Date
2 | User 2 | Date
3 | User 3 | Date
5 | User 1 | Date
6 | User 1 | Date
7 | User 2 | Date
8 | User 1 | Date

need all rows where collumn "user name" == User 1 upper in ordering

order column "id" ASC and all collumn "user name" == User 1 upper in ordering

User 1 First order
1 | User 1 | Date
5 | User 1 | Date
6 | User 1 | Date
8 | User 1 | Date

and asc/desc other rows
2 | User 2 | Date
3 | User 3 | Date
7 | User 2 | Date

rf1234

https://datatables.net/reference/option/order

order: [
        [1, 'asc'],
        [0, 'asc']
    ]

But I don't think you want that. You seem to want completely separate ordering depending on the "user" being User 1 or some other user.

You can't do that client side I am afraid.

I did something similar on the server using the "postGet" event and then manipulating $data which can be passed by reference.
https://editor.datatables.net/manual/php/events#Get

On the client I turned off the automatic ordering like this:

order: [], //no ordering by Data Tables

allan

Yeah, I'm still not 100%. Is you want "User 1" to be the primary sort, then id, but if not "User 1" then "id" should be the primary sort? Not sure I've ever encountered that before.

You'd need to have a column that contains both data points and have a custom data type sort function that would do that. Perfectly possible, but a little bit of coding required.

Allan

rf1234

This should do the job on the server:

->on( 'postGet', function ( $e, &$data, $id ) { 
    if ( count($data) <= 0 ) {
        return;
    }
    $user1 = $others = [];
    
    foreach ( $data as $row ) {
        if ( $row["user_name"] === "User 1" ) {
            $user1[] = $row;
        } else {
            $others[] = $row;
        }
    }
    
    //sort user 1 by id ascending
    array_multisort( array_column($user1, "id"), SORT_ASC,
                     $user1 );
    
    //sort other users by id ascending, too
    array_multisort( array_column($others, "id"), SORT_ASC,
                     $others );
    
    $data = array_values(array_merge($user1, $others));
    
})

Use that with this on the client side:

order: [], //no ordering by Data Tables

allan

Very clever - like it! Just make sure ordering is disabled or set to [] on the client-side so DataTables doesn't resort.

Allan

rf1234

Thanks Allan! I think doing special formatting server side is a lot easier than doing it with Java Script in the browser. And it's probably faster, too. In this example I add a summation row to the data table inside field type "datatable". This wouldn't work with client side ordering just like in the example above: Client side ordering would always put the summation row in the wrong position.

I normally only return formatted values from the server. Here I also read them unformatted to be able to order them and sum them up. Afterwards I simply unset the "helper fields".

What I really like is the flexibility that this provides: If I didn't have this I would need to do this all outside of Editor. It's great that I can keep using Editor even for "special cases" like this one.

->on( 'postGet', function ( $e, &$data, $id ) use ( $lang ) { 
    if ( count($data) <= 0 ) {
        return;
    }
    //sort the array by due date ascending and amount descending
    array_multisort( array_column($data, "unformattedDueDate"), SORT_ASC,
                     array_column($data, "unformattedAmount"), SORT_DESC,
                     $data );

    $sum = array_sum(array_column($data, "unformattedAmount"));
    $sumPaid = array_sum(array_column($data, "unformattedAmountPaid"));
    $keys = array_keys($data);
    foreach ( $keys as $key ) {
        unset($data[$key]["unformattedAmount"]);
        unset($data[$key]["unformattedAmountPaid"]);
        unset($data[$key]["unformattedDueDate"]);
    }  

    $subAmount = getSubAmount( $_POST['ctr_id'] );

    $subAmountString = $lang === "de" ? "<br>(Zuwendung nur: " : "(Grant only: ";
    $subAmountString .= ( getFormatterAmount($subAmount) . ')' );

    if ( abs($subAmount) < abs($sum)  &&  $subAmount != 0 ) {
        $cfAmount = '<span class="text-danger">' . getFormatterAmount($sum) . $subAmountString . '</span>';
    } else {
        $cfAmount = getFormatterAmount($sum);
    }
    if ( abs($subAmount) < abs($sumPaid)  &&  $subAmount != 0 ) {
        $cfAmountPaid = '<span class="text-danger">' . getFormatterAmount($sumPaid) . $subAmountString . '</span>';
    } else {
        $cfAmountPaid = getFormatterAmount($sumPaid);
    }

    if ( $sum != 0 || $sumPaid != 0 ) {
        $data[] = [ "DT_RowId" => "row_0", //in reality there is no row 0 because it is derived from the SQL id
                    "sub_exec_cashflow" => 
                        [ "ctr_id"                  => $_POST['ctr_id'], 
                          "cashflow_due_date"       => $lang === "de" ? "Summe" : "Total",
                          "cashflow_amount"         => $cfAmount,
                          "first_reminder_date"     => "", 
                          "second_reminder_date"    => "",
                          "cashflow_call_date"      => "",
                          "cashflow_amount_paid"    => $cfAmountPaid ]   ];
    }
})

And this is what it looks like in reality inside the Editor modal.

allan

Yup - that is very nice and a great use case / example. Thank you!