Set ORDER BY on server side

marwi

Hi,
Is there any way to order the columns initially in the PHP server file? (using the PHP library)
I have found the order('col1,col2') function, however it seems I cannot use it within Editor::inst()->... calls.
This would help to order the columns by name instead of index.
Thanks and best regards

allan

The column order in the JSON should actually be irrelevant. It doesn't matter if the JSON is:

{
  "columnA": 1,
  "columnB": 2
}

or

{
  "columnB": 2,
  "columnA": 1
}

it contains exactly the same information.

Ordering isn't important in a Javascript object for the parameter names since you can loop over it many different way.

Also, Editor uses PHP's json_encode() method, so its at the mercy of whatever order it decides to output the parameters. As such, I'm afraid there isn't an option for this.

I'm not actually clear on what the use case for it is?

Thanks,
Allan

marwi

Thanks for you reply.
Use case: I plan to distribute a similar DataTable file in various versions: Always based on the same big table, each version includes only some of the available columns and the number of columns also varies. The last two columns, however, are always the same and the initial ORDER BY should be defined there.
I could use the table option order: [[6,'asc'],[7,'asc']], but the index numbers will change for each version. If there was an option to order by "column name" instead, it would not be necessary to adjust this setting for each version. So that's how I came to ask my question, if possible to do in PHP/SQL already.

Maybe there is some JS trick to lookup the column index by column name? If so, the order might somehow be possible to set right after the initialization of the data table.

allan

An ORDER BY will effect the order of the rows, not the properties in each row. However, I see your point about needing the default sort order to be flexible.

Do you know before you load the Ajax data what it will be? You could make it dynamic at that point. Otherwise, if it is information you can return from the server to the client you could use order() to set the ordering.

Allan

marwi

Yes, I know the names of the columns to be sorted on already before the AJAX load (for example 'customer' and 'invoice'). But how to make it dynamic at that point?

allan

How do you know the names and how they are shown in the JSON? Can you show me a sample of the data please?

Allan

marwi

Sample JSON data received from server:

{
  "data": [
    {
      "DT_RowId": "row_1",
      "shdms": {
        "id": "1",
        "factory": "Wenta",
        "inspection": "",
        "mi_amount": "",
        "created": "",
        "revised": "",
        "invoice": "IS1801307",
        "customer": "Mann",
        "paymnt_status": "abc",
        "acct_status": ""
      }
    },
    {
      "DT_RowId": "row_2",
      "shdms": {
        "id": "2",
        "factory": "Danu",
        "inspection": "PASS",
        "mi_amount": "123456.12",
        "created": "2017-12-29 10:11:22",
        "revised": "",
        "invoice": "IS1812346",
        "customer": "Aldi",
        "paymnt_status": "new",
        "acct_status": "new"
      }
    }
  ],
  "options": [],
  "files": []
}

Let's say the table should initially be sorted by Customer and Invoice. Is there any way in JavaScript to read the indexes for the columns named Customer and Invoice from the JSON data? If so, I could determine the indexes first and then call order() from the API:

var customer_index = 7; // should be dynamic
var invoice_index = 6; // should be dynamic

var table = $('#shdms').DataTable();
table
    .order( [ customer_index, 'asc' ], [ invoice_index, 'asc' ] )
    .draw();

allan

An object in Javascript is unordered data (e.g. note how acct_status is after payment_status in the data shown above - it isn't alphabetical - it isn't anything!).

So you must have something else that is telling DataTables which data point to show in each column? That is columns.data.

Allan

marwi

Are you talking about the sequence of the columns? In SQL this would be defined like SELECT col1,col2,col3. However, my question is rather about the sequence of the alphabetically ordered columns. In SQL it would refer to ORDER BY column1, column2, column3

allan

Ah - okay, I'm with you now.

You can use column().dataSrc() to determine the data source property for a column - e.g. in the case above you would loop over the columns looking for the dataSrc with a property of acct_status. That would then give you the index for that column.

Allan