Ordering when merging 2 columns in 1

malik112

Hi,

I am using server side processing to display my data table.
I have 2 date columns in the database. For every record in the database table, one of the date columns (lets call it Col #7) has either a NULL value or a date. The other date column (lets call it Col #0) always have the current timestamp to when the record was added.

I brought in both columns via the server processing php script as column 0 and column 7.. then in my javascript on the HTML page, I check if col 7 is not empty.. if its NOT empty then it writes the value into Col 0 and hides the value of Col 0 in that row. If, however, Col 7 is empty, then Col 0 value is shown. Lastly, I hide Col 7 from the front end.

My ques is this -
What should I do to make the column ordering possible at Col 0 in this case to also consider the values of Col 7 when ordering them (asc or desc.).. Currently it orders on Col 0 asc which means that Col 7 values are inserted somewhere in between and are not taken into account.

Here is my code on the Javascript:

                    "columnDefs": [
                        {
                            "render": function ( data, type, row ) {
                                if (row[7]== "0") {
                                    return data;
                                } else {
                                    return row[7];
                                }
                            },
                            "targets": 0
                        },
                        { "visible": false,  "targets": [ 7 ] }
                    ],

Thanks in advance.

P.S. I am currently a beginner and just started using DataTables and I feel awesome. But I would request you to please breakdown the solution for a newbie. Thanks thanks thanks ;-)

allan

Since you are using server-side processing, the ordering is performed by the server. I'm not sure what server-side processing script you are using, but you would need to modify that to apply the sorting you want on the columns you want when sorting is activated on column 0.

Alternatively - do you really need server-side processing? Are you using tens of thousands or more of rows?

Allan

malik112

Hi Allan

Thanks for your response. I am not using tens of thousands or more rows at the moment but I think it may soon grow to that number. Moreover, I could not find any other way than server-side processing. I am using the server_processing.php file that comes with datatables download.. havent made any changes to it except the last line json_encode which now looks like

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "user_id = $userid" )
);

where $userid is the ID of the logged in user for him to see only his data in the data table.

What other method do you recommend please?
And if I have to order the fetched data server side, do you have any code recommendations on that?

Thank you very much for your help!

malik112

Hey Allan,

Can you please take out sometime to explain this or please point me to a help page where i can find this info.. thanks a ton...

allan

I fear that the demo SSP class isn't going to handle this. It really is meant only as a simple implementation to help get started. It can be used for the majority of cases, but for more complex interaction such as conditional SQL statements, which is sounds like what you want, it would need to be modified or a different method used.

Consider using columns.orderData. That way you can tell DataTables to multi-column order, which will work with the SSP class. If the data is the same in the first column, it will sort by the data in the second column (which I think is what you want?).

That only works for the most simple of conditional cases though. If there is more complex logic than that, then an SQL VIEW might be in order. Then you can use the SSP class on the resulting view.

Regards,
Allan

malik112

Hey Allan

Thanks for your response. Sure so I tried the column.orderData and it does not work either. The second option that you have - the SQL view. Where can I find more information about how this can be used with the SSP class? How do we create a view and use the SSP class?

Thanks! I hope thats my last question :-)

Regards
Malik

allan

Sure so I tried the column.orderData and it does not work either

In what way. Errors? Incorrect sorting?

How do we create a view

You'd need to refer to the documentation for for whatever SQL server you are using. CREATE VIEW is the normal one, similar to CREATE TABLE.

and use the SSP class?

Once the VIEW is in place you can use the SSP class on it just like you would do a table, since you can do a SELECT on the view.

Allan