Problem with Where-Clause in DataTables Editor

schwaluck

Hi all,

sorry for asking another question...
I have the following problem:

I implemented a where-clause for my table, which works just fine. Heres the clause (PHP):

->where( 'Zeit_abholen', 'cast((now()) as date) and Zeit_abholen < cast((now() + interval 1 day) as date)
                        or (Zeit_abholen >= cast((now() - interval 1 day) as date) && Zeit_zurueck is Null)
                        or (Zeit_abholen >= cast((now() - interval 1 day) as date) && Zeit_zurueck >= cast((now()) as date))', '>=', false );

I know its dirty programmed and not fancy but I also tried the other way around with splitting it up into and_where's and or_wheres.

If I know insert a new entry into my table with Editor, it actually doesnt display the new entry but rather the first entry in my table.
Heres my http request and response:

Request:
data[0][Marke]: "Audi"
data[0][Kennzeichen]: "de"
data[0][Zeit_abholen]: "2020-07-04 14:25:21"
data[0][Fahrer_abholen]: "Audi"
etc.

Answer (equal to first entry in the table):
0   Object { DT_RowId: "row_1", id: "1", Marke: "Audi", … }
DT_RowId    "row_1"
id  "1"
Marke   "Audi"
Kennzeichen "DOOF"
Zeit_abholen    "2020-07-04 13:52:21"
Fahrer_abholen  "DOOF"
Zeit_zurueck    null
Fahrer_zurueck  null
Kindersitz  null
Navi    null
Besonderheiten  null
Preis   "13.9"
Status  null

If I remove the where-Clause, it works just fine. Also, the right values are loaded when the table is refreshed.
In the database the right values are also stored.

I am not able to identify my mistake here.

Any help is highly appreciated!

Paul

schwaluck

Some Pictures for clarification:

Thats what I enter in my Editor instance:

Thats what I get as an output:

And thats how it looks like after refreshing:

allan

Hi Paul,

Where exactly is your where statement? The Editor class doesn’t allow a four parameter where statement (i.e. the fourth to indiciate that it should not be bound) - you need to use a closure for that:

Editor::inst(...)
  ->fields(...)
  ->where( function ($q) {
    $q->where(‘cast((now()) ...’);
  } )
  ->process(...)
  ->json();

Allan

schwaluck

Hey Allan,

thanks for taking a look! That must have been my mistake.
It works fine now.

The alternative I discovered was using a SQL view with the given where clause, so that the data was already filtered.

Have a great week,
Paul