Use SQL-Cases in Datatables

schwaluck

Hi all,

I previously worked with a tool where I was able to define the SQL-query to the table myself. Within this tool I used the following sql-query to set a column "Status":
CASE
WHEN TIMESTAMPDIFF (MINUTE, time_to, NOW()) > 45 && time_from is Null
THEN 'Late'
ELSE 'On-Time'
END Status

What would be the best way to do this in DataTables Editor?
I already have a function which automatically reloads the table every 60 seconds.

I am glad for any help!

Thanks a lot!
Cheers,
Paul

faxunil

Hi!
I created a view and joined to my table

schwaluck

Hey, thanks for your help!
I'll try it that way.

faxunil

I hope it works.

schwaluck

Hey all,

I tried the following:

I created a view in my Backend, which works fine. The status is set as I wanted it to be.
Now I wanted to display the view, as described here:
https://editor.datatables.net/examples/advanced/sqlView.html

My view is named "7".

In my case:
Editor::inst( $this->editorDb, $this->table, 'id' )
->readTable('7')
->fields(.....
....

Sadly, it doesn't work and the datatable is loading the base table.
Still, the code is executed and if I write a wrong name into the ->readTable, I get an error message.

Does anyone have an idea what I am missing at the moment?

Thanks and enjoy your evening!
Paul

schwaluck

Hey,

I figured my problem out:
My SQL-Case Syntax was false and the Status wasn't saved in the column but my view rather created a new column. I didnt display that one in the frontend...

So yeah, everything worked fine.

Thanks again for the help!!!

rf1234

@schwaluck You'll find Editor extremely flexible when it comes to retrieving and rendering all kinds of values with custom getFormatters particularly if the fields are read only.

I previously worked with a tool where I was able to define the SQL-query to the table myself.

With Editor you can even do this on a field by field basis: You can write proprietary SQL for every field Editor retrieves using custom getFormatters. This can be very useful for complex rendering.
https://editor.datatables.net/manual/php/formatters#Custom-formatters

You can use your own db handler in the getFormatter or the Editor db handler as well like in here:

Field::inst( 'status' ) -> set( false )
    ->getFormatter( function ( $val, $data, $opts ) use ( $db) {
        $statement = ('SELECT something
                        FROM yourTable
                   INNER JOIN b ON ...
                        WHERE status = :status');  
         $result =
         $db ->raw()
             ->bind(':status', $val)
             ->exec($statement);
         $row = $result->fetchAll(PDO::FETCH_ASSOC);
         foreach ($row as $val) {
             do something
         }
         return something //e.g. comma separated list of values as a string
    } )