Use SQL-Cases in Datatables

Use SQL-Cases in Datatables

schwaluckschwaluck Posts: 103Questions: 27Answers: 1

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

This question has accepted answers - jump to:

Answers

  • faxunilfaxunil Posts: 2Questions: 0Answers: 2
    Answer ✓

    Hi!
    I created a view and joined to my table

  • schwaluckschwaluck Posts: 103Questions: 27Answers: 1

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

  • faxunilfaxunil Posts: 2Questions: 0Answers: 2
    Answer ✓

    I hope it works.

  • schwaluckschwaluck Posts: 103Questions: 27Answers: 1

    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

  • schwaluckschwaluck Posts: 103Questions: 27Answers: 1

    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!!!

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    @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
        } )
    
This discussion has been closed.