Custom field in Editor Serverside script

mihalisp

Hi,
when using serverside, i want in the serverside script to to make-use a custom Editor field (dates-datetime difference in HH:mm ) so that it can be easily sorted in the Datatable.
I currently have it as rendered column in Datatable ,but i can't sort it since it does not exist in the Serverside Editor script .

Thank you.

kthorngren

Just to clarify, the Editor fields are not involved with sorting. The Datatables fields are sorted. Your server side script will need to perform the datetime difference to provide sorted data on that column. To help we will need to know more about your server script.

1. Are you using a Datatables provide script or your own custom script?
2. What is the datetime difference calculation you have?
3. Maybe you can build your DB query to perform this difference, what is your database?

Kevin

mihalisp

I use SQL Server and Datatables provide script like these you provide in your examples section.

In another Datatable i have used my own Sql script with classic Sql in which i calculate datetimes difference like this:

   CONVERT(varchar(5),DATEADD(minute,DATEDIFF(minute,start,end),0), 114)

mihalisp

I want to know if there is a way to calculate the datetime difference Field by using a render function inside that Field.I don't know how to do it in Datatables' serverside style Sql.

allan

If you are using server-side processing (serverSide) then the sorting is all done by the server-side (e.g. the SQL server). Are you using server-side processing or client-side?

If you could link to a page showing the issue that would be very helpful.

Allan

mihalisp

I just want to calculate datetime difference inside the:

  ->field(
    Field::inst( '.......calculated_field' ) //calculated_field in what table ??
            ->render( function ( $row ) {
                    return ......... ;  } )
            )

allan

I'm still not clear if you are using server-side processing or not. Could you clarify that please as it will effect the answer.

You should actually be able to call SQL functions inside the Field - e.g.:

Field::inst('CONVERT(varchar(5),DATEADD(minute,DATEDIFF(minute,start,end),0), 114)', 'calculatedDiff')

and then use calculatedDiff on the client-side.

If that doesn't work, please give me a link to a page showing the issue so I can take a look into it.

Thanks,
Allan

mihalisp

I use Serverside.
It may be simpler than that Allan and not use the Convert function.
What i mean:

I have 2 datetime fields (Field1,Field2) as follows:

     ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) ).

and i want in a 3rd custom -calculated Field (as Field3) to find the difference in format HH:mm.

Thank you!

mihalisp

Well,that worked,but it does not sort when i use serverSide.it sorts only in client side mode.

Should i use ->getformatter so that the sorting works in serverSide?

Can you give me an example?

In general,when serveSide mode is used,how can i achieve the Editor fields to be well sorted and searched?

Thank you.

colin

I suspect there's a misunderstanding here. DataTables displays the table data, Editor displays the editing data.

So, in the normal table view, whether you're using serverSide or not, DataTables controls the data. If you've got serverSide set, your server-side script will determine the ordering, paging and searching - if not, that's done by the client. That's all in the hands of DataTables, and is independent of Editor.

If you edit a cell or an entire record, the formatting in that edit mode is determined by the Editor settings.

Hope that helps,

Colin

mihalisp

I am sorry,but i am still confused.

1) I want to use Datatables to get data from a view and an Editor to edit only some fields of the view's 'master' table.Is that clear?
As long as i use Editor with a Datatable,i use the Editor Fields to populate Datatable ->field( Field::inst( 'field1' ) ).Is this correct?It is the same in your examples.

If i am right,in order to populate datatable from a view
i use the ->readtable('view') in Editor instance ,but i get error The multi part identifier... could not be bound.
I read somewhere that it may have to do with the table alias i should use in JOINS in the VIEW.

Since the Datatable and Editor are related to each other,should the field names or field aliases in the Sql View have to be the same as in the Editor fields(also aliases)?

2) For the calculated field 'calculatedDiff' in Editor and Datatable i use the following as you suggested and it works.But the sorting works well only in client mode.

Field::inst('CONVERT(varchar(5),DATEADD(minute,DATEDIFF(minute,field1,field2),0), 114)', 'calculatedDiff')

if i use serverSide ,i get the following error when i try to sort that field (HH:mm).
SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'asc'

How to make it sort in ServerSide?

Thank you again.

mihalisp

Colin,Allan any help please?

allan

Sorry for the slow replies over Christmas / New Year. And thanks for the extra information.

If you want to use server-side processing and also want to be able to sort the calculated column, then the calculation must be done at the server-side. It cannot be done client-side.

It sounds like a VIEW probably is the best way to go here. I had expected the CONVERT(var... stuff to work, but it looks like the driver you are using doesn't like using that in the ORDER BY. Fair enough - we could either alias it (which isn't currently supported in the Editor libs) or use a VIEW. A VIEW is how I'd go about it.

Could you show me the code you are using for that please, resulting in the error you mentioned?

Allan

mihalisp

I will try to make it work and i 'll let you know.
The fields in the Editor should be the same (or subset) with the fields in the VIEW?

How the server makes the column-calculated field sorting?i mean,does it need extra sql code to sort the column in a right way?

Is this necessary for all the columns in serverSide or only for calculated fields?

In the same Editor-Datatable fields i have a date column (get formatter d/m/Y H:i ) Editor field which auto sorts properly when i click on the column header in Datatable.

i also use the datetime plugin which works only in client mode ,right?

Thank you

allan

The fields in the Editor should be the same (or subset) with the fields in the VIEW?

Generally I make it so, since its easier. But it isn't required. You could add ->set(false) to the read fields and ->get(false) to the write fields.

How the server makes the column-calculated field sorting?i mean,does it need extra sql code to sort the column in a right way?

If the calculation is part of the VIEW the SQL engine will do it automatically.

Is this necessary for all the columns in serverSide or only for calculated fields?

All columns you want to show should be selected. Think of the VIEW as just a SELECT.

i also use the datetime plugin which works only in client mode ,right?

Correct. If you are using server-side processing with date / time / datetime columns in the database, the db will automatically do the correct sorting.

Allan