Filter in Serverside mode

mihalisp

Hi everyone,
I am trying to filter an SQL Server query in ServerSide by date values from POST(2 bootstrap datetime pickers in client side).
On that Datatable i also use Editor.

My code doesn't work and i can't figure why.

I don't know if the problem is the date types that are compred(Date or string?) or the syntax i use on the WHERE clause.

The serverside code is below:

     Editor::inst( $db, 'db1' ,'table_id')
 ->field(
    Field::inst(..............),

        Field::inst( 'table.startdate' )
    ->validator( Validate::dateFormat('d/m/Y H:i',ValidateOptions::inst()
                                          ->allowEmpty( false )
    ) )

    ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) )
    ->setFormatter( Format::dateTime( 'd/m/Y H:i', 'Y-m-d H:i:s' ) )
        ,
    Field::inst( 'table.enddate' )
    ->validator( Validate::dateFormat('d/m/Y H:i',ValidateOptions::inst()
                                              ->allowEmpty( false )
    ) )

    ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) )
    ->setFormatter( Format::dateTime( 'd/m/Y H:i', 'Y-m-d H:i:s' ) )    ,

    )

    ->leftJoin( '.....', '.....',     '=', '.......' )
->where( $_POST['datevalue_from_datepicker1'], 'table.startdate', '<=' )
->where( $_POST['datevalue_from_datepicker2'], 'table.startdate', '>=' )

->process($_POST)
->json();

//echo json_encode( );  where should i put these 2 lines to track the progress of the query??
//var_dump($_POST);

and the Datatable:

   var example_table = $('#example').DataTable( {

    serverSide:true,
    processing:true,
    ajax: {
        url: "edit.php",
        type: 'POST',
        "data": function ( d ) {
    //d.datevalue_from_datepicker1= $('#datepicker1').val();  //this returns string
    //d.datevalue_from_datepicker2= $('#datepicker2').val();  //this returns string

    d.datevalue_from_datepicker1= $('#datepicker1').datepicker( 'getUTCDate' ); //this returns Date object
    d.datevalue_from_datepicker2= $('#datepicker2').datepicker( 'getUTCDate' ); //this returns Date object
}
    },.........

In Network tab, I get error

  {"fieldErrors":[],"error":"SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]An expression of non-   boolean type specified in a context where a condition is expected, near 'Jun'.","data":[],"ipOpts":[],"cancelled":[]}

What is the proper way to compare dates in php serverside script? I am confused with date trasnformations...

Thank you.

allan

Just to be clear - the issue here is that you can't use the global search box to perform a search on the date format that is being shown to your end user?

If so, the issue is due to the fact that the search is performed in SQL, but the formatting (getFormatter) is performed in PHP (i.e. after the SQL search has already been done).

So I guess the first question is - how many records do you have? If you can use client-side processing, then this isn't an issue since it will search on the rendered data.

If you can't do that, then you need to deformat the search term into ISO8601 so the database and search on the string given.

Allan

mihalisp

Well,
i have 100.000 rows JSON which is about 40Mbytes load on the client side and so the whole browser freezes.That's why i use serverside.

So,i want the filter to be done in SQL so the returned JSON is limited to 200-300 kbytes.
I have already done client side filtering with success in other pages of my application.

In the database the date format is Datetime.

What should i change in serverside php script in your opinion?
What is the right date format i should use in the 'WHERE' clause in order to perform the comparison with success?
Is there any other way you may suggest to perform the filter in sql based on dates from the Datepickers?
Can you give me an example please?

allan

Yup - server-side processing it is then!

The difficult thing about deformatting a search string into a date format, is that you are also searching for other string terms from other columns. So for example "Mar" might be the month of March or it might refer to the planet "Mars" if you had a database of planets...

I'm tempted to suggest that in this case the date formatting be done in the SQL. Then the SQL server will be doing all of the heavy work for us. We could use the preGet event handler, plus the libraries ability to use functions to do this - e.g.:

->field( new Field('startdate') ) // field definition for write
->on( 'preEdit', function ($editor) {
  // Get the field so we can modify its properties
  $field = $editor->field('startdate');

  // Change the field's get definition for reading
  $field->dbField( 'DATE_FORMAT(startdate, "%d/%m/%Y %h:%i:%s" )' );
} )

I think that's going to be the best way of doing it...

Allan

mihalisp

Thank you,but i am still missing something.

I don't quite understand your code.

My Editor and Datatable work fine right now as they are configured.

What i just want is the right way to make the comparison in the 'WHERE' clause so that the JSON returns filtered (between 2 dates) and smaller in size.So the Datatable will display only a filtered portion of the whole table in db (eg 1000 rows of the 100.000).

My problem is not in the Editor itself (new,edit or duplicate) but in the recordset returned to be displayed in the Datatable.

Shoyuld i first transform the Date in $_POST['datevalue_from_datepicker1'] or the 'table.startdate' field from db?How to transform them to the same type?

Maybe i didn't make myself clear.

In another page i use the following classic PHP SQL Serverside script (without Editor in this case) to filter the query with the Date strings from the Datepickers.

    where startdate >= (convert (date,SUBSTRING(CONVERT(varchar, ?, 100), 4, 3) 
                       + SUBSTRING(CONVERT(varchar, ?, 100), 1, 3)
                       + SUBSTRING(CONVERT(varchar, ?, 100), 7, 4)))  and 
        startdate <= (convert (date,SUBSTRING(CONVERT(varchar, ?, 100), 4, 3) 
                       + SUBSTRING(CONVERT(varchar, ?, 100), 1, 3)
                       + SUBSTRING(CONVERT(varchar, ?, 100), 7, 4)))  

I think this is a case that many others here would have faced-dealt with.

Allan,thank you anyway.

allan

Oh! I completely misunderstood the issue. Sorry!

->where( $_POST['datevalue_from_datepicker1'], 'table.startdate', '<=' )

would do if you have the datevalue_from_datepicker1 in ISO8601 format. You'll probably need to use PHP's strtodate() and then date() functions to convert the submitted format to ISO8601.

Allan

mihalisp

I already have $_POST['datevalue_from_datepicker1'] in ISO8601 format (I get : Date 2019-06-11T22:29:41.336Z,in browser's console)
I use

 "data": function ( d ) {

d.datevalue_from_datepicker1= $('#datepicker1').datepicker( 'getUTCDate' ); //this returns Date object
d.datevalue_from_datepicker2= $('#datepicker2').datepicker( 'getUTCDate' ); //this returns Date object
    }

so where is the problem?
Should i also format properly the table.startdate db field ?
Get or Set Formatter plays role in the format compared?
If i understand well ,i should format d/m/Y H:i from GetFormatter to ISO8601 using strtoDate or the opposite ,that is to format the ISO8601 Date from $_POST['datevalue_from_datepicker1'] to d/m/Y H:i ???

Field::inst( 'table.startdate' )
->validator( Validate::dateFormat('d/m/Y H:i',ValidateOptions::inst()
                                      ->allowEmpty( false )
) )

->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) )
->setFormatter( Format::dateTime( 'd/m/Y H:i', 'Y-m-d H:i:s' ) )
    ,

​

mihalisp

I have tried many combinations,but nothing.
I get datepicker date either as a string or a Date ,i try to format the string with strtotime but i still get error Invalid column name.

Why is the dates manipulation so weird?

mihalisp

After researching for many hours,i finally found a way to make it work.

I pass the POST date as string and then format it with strtotime() and then date() as follows:

    $POST1 = $_POST["datevalue_from_datepicker1"] ;
    $POST1 = str_replace('/', '-', $POST1); 
    $POST1 = date('Y-m-d', strtotime($POST1));

and in sql in Editor instance

   ->where( function ( $q ) use ( $POST1) { 
    $q->where( 'start',$POST1,'>=' );  } )

Thanks for the help!