sort date fields, using moment?

sort date fields, using moment?

cris19ncris19n Posts: 55Questions: 18Answers: 0
edited September 2021 in DataTables

I am using server side to fetch my data.
I'm rendering my date data to show it in the desired am / pm format.

but when sorted by this field it doesn't sort as it should, sort out of order.

add this line of code in this format which is the same as I am rendering, but my ordering still does not work correctly

$.fn.dataTable.moment('YYYY-MM-DD hh:mm A', 'es');

this is an example of that field and my data table setup, using server side. I want to sort by data 10

let tablep1=$('#piku-produccion').DataTable({
    dom: 'Blf<"#searchCodPpro">rtip',  //<"#SearchProduccion">
    "language": {
        "sProcessing":     "<img src='vistas/img/plantilla/200.gif' />",
        "sLengthMenu":     "MOSTRAR _MENU_ REGISTROS",
        "sZeroRecords":    "NO SE ENCONTRARON RESULTADOS",
        "sEmptyTable":     "Ningún dato disponible en esta tabla",
        "sInfo":           "MOSTRANDO REGISTROS DEL _START_ AL _END_ <br> DE UN TOTAL DE _TOTAL_",
        "sInfoEmpty":      "MOSTRANDO REGISTROS DEL 0 AL 0 DE UN TOTAL DE 0 <br>",
        "sInfoFiltered":   "(FILTRADO DE UN TOTAL DE _MAX_ REGISTROS)",
        "sInfoPostFix":    "",
        "sSearch":         "<i class='fa fa-search text-info'></i>",
        "sUrl":            "",
        "sInfoThousands":  ",",
        "sLoadingRecords": "CARGANDO...",
        "oPaginate": {
        "sFirst":    "PRIMERO",
        "sLast":     "ULTIMO",
        "sNext":     "SIGUIENTE",
        "sPrevious": "ANTERIOR"
        },
        "oAria": {
            "sSortAscending":  ": Activar para ordenar la columna de manera ascendente",
            "sSortDescending": ": Activar para ordenar la columna de manera descendente"
        },
        "buttons": {
            "copyTitle": 'agregado al porta papeles',
            "copyKeys": 'Seguir adelante <i>ctrl</i> o <i>\u2318</i> + <i>C</i> para copiar los datos de la tabla a su portapapeles. <br><br>Para cancelar, haga clic en este mensaje o presione Esc.',
            "copySuccess": {
                _: '%d líneas copiadas',
                1: '1 línea copiada copiada'
            }
        }

    },
    "processing": true,
    "serverSide": true,
    "searchDelay":2000,
    "sAjaxSource":'serverside/show.tabla.php',
    search: {search: respuesta["lote_piku"]},
    "ordering": true,
    "order": [[10, "desc"]],
    "columns": [
        { data: 1 },
        { data: 2 },
        { data: null,
            "render": function ( data, type, row ) {
                //let dt1=row[3];
                 let dt1 =`
                            <span class="badge bg-yellow pull-right" data-toggle="tooltip" data-placement="right" data-html="true" title="Esta campo <br> no tiene lote reproceso">
                            LOTE RE-PROCESO ?
                            </span>
                        `
                ;
                //console.log(row[3]);
                if (row[3]!==null) {
                   dt1=row[3];
                }else{

                }

                return dt1;

            }
        },
        { data: 4 },
        { data: 5 },
        { data: 6 },
        { data: 7 },
        { data: 8 },
        { data: 9 },
        {//this is the field that I am formatting with moment and the field that I want to sort
            "render": function ( data, type, row ) {
                //2021-08-09 11:27 AM or 2021-08-09 02:27 PM
                let dt1 = moment(row[10], ["YYYY-MM-DD HH:mm:ss"]).format("YYYY-MM-DD hh:mm A");

                return dt1;

            }
        }
    ],
    "lengthMenu": [[500, 250, -1], [500, 250, "TODO"]],
    /*activo la el scroll dentro de la tabla para poder depararme y obtener vista de todas las columnas-filas */
    "scrollY": 300,
    "drawCallback": function(row, data, index) {
        $('[data-toggle="tooltip"]').tooltip();
    },
    "initComplete": function(){
        //para evitar que el servidor y el renderizado colapse, haciendo llamadas ajax
        $("#piku-produccion_filter input").unbind().bind("input", function(e) { // Bind our desired behavior
            // If the length is 6 or more characters,search
            if(this.value.length == 6) {
                // Call the API search function
                tablep1.search(this.value).draw();
            }
        });
    }
});

note: for some unknown reason it correctly sorts the first data load in the table, but when I apply the order by clicking the field, everything gets messed up.

Replies

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,735

    When using server side processing the sorting, searching and paging is controlled by the server script. Using $.fn.dataTable.moment('YYYY-MM-DD hh:mm A', 'es'); will only work with client side processing. Are you using a Datatables supplied server side processing script? Do you need server side processing?

    Kevin

  • cris19ncris19n Posts: 55Questions: 18Answers: 0

    if i am using server side processing.

    file. serverside/show.tabla.php

       <?php
        require 'serverside.php';
        $table_data->get('view_tabla', 'cod', array('cod','data1','data2','data3','data4','data5','data6','data7','data8','data9','data10'));
    

    file serverside.php

    <?php
    include 'serversideConexion.php';
    class TableData {
        private $_db;
        public function __construct() {
            try {
                $host       = HOST_SS;
                $database   = DATABASE_SS;
                $user       = USER_SS;
                $passwd     = PASSWORD_SS;
    
                $this->_db = new PDO('mysql:host='.$host.';dbname='.$database, $user, $passwd, array(
                    PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
            } catch (PDOException $e) {
                error_log("Failed to connect to database: ".$e->getMessage());
            }
        }
        public function get($table, $index_column, $columns) {
            // Paging
            $sLimit = "";
            if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' ) {
                $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".intval( $_GET['iDisplayLength'] );
            }
    
            // Ordering
            $sOrder = "";
            if ( isset( $_GET['iSortCol_0'] ) ) {
                $sOrder = "ORDER BY  ";
                for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ ) {
                    if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) {
                        $sortDir = (strcasecmp($_GET['sSortDir_'.$i], 'ASC') == 0) ? 'ASC' : 'DESC';
                        $sOrder .= "`".$columns[ intval( $_GET['iSortCol_'.$i] ) ]."` ". $sortDir .", ";
                    }
                }
    
                $sOrder = substr_replace( $sOrder, "", -2 );
                if ( $sOrder == "ORDER BY" ) {
                    $sOrder = "";
                }
            }
    
            /*
             * Filtering
             * NOTE this does not match the built-in DataTables filtering which does it
             * word by word on any field. It's possible to do here, but concerned about efficiency
             * on very large tables, and MySQL's regex functionality is very limited
             */
            $sWhere = "";
            if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
                $sWhere = "WHERE (";
                for ( $i=0 ; $i<count($columns) ; $i++ ) {
                    if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" ) {
                        $sWhere .= "`".$columns[$i]."` LIKE :search OR ";
                    }
                }
                $sWhere = substr_replace( $sWhere, "", -3 );
                $sWhere .= ')';
            }
    
            // Individual column filtering
            for ( $i=0 ; $i<count($columns) ; $i++ ) {
                if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
                    if ( $sWhere == "" ) {
                        $sWhere = "WHERE ";
                    }
                    else {
                        $sWhere .= " AND ";
                    }
                    $sWhere .= "`".$columns[$i]."` LIKE :search".$i." ";
                }
            }
    
            // SQL queries get data to display
            $sQuery = "SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $columns))."` FROM `".$table."` ".$sWhere." ".$sOrder." ".$sLimit;
            $statement = $this->_db->prepare($sQuery);
    
            // Bind parameters
            if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" ) {
                $statement->bindValue(':search', '%'.$_GET['sSearch'].'%', PDO::PARAM_STR);
            }
            for ( $i=0 ; $i<count($columns) ; $i++ ) {
                if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' ) {
                    $statement->bindValue(':search'.$i, '%'.$_GET['sSearch_'.$i].'%', PDO::PARAM_STR);
                }
            }
    
            $statement->execute();
            $rResult = $statement->fetchAll();
    
            $iFilteredTotal = current($this->_db->query('SELECT FOUND_ROWS()')->fetch());
    
            // Get total number of rows in table
            $sQuery = "SELECT COUNT(`".$index_column."`) FROM `".$table."`";
            $iTotal = current($this->_db->query($sQuery)->fetch());
    
            // Output
            $output = array(
                "sEcho" => intval($_GET['sEcho']),
                "iTotalRecords" => $iTotal,
                "iTotalDisplayRecords" => $iFilteredTotal,
                "aaData" => array()
            );
    
            // Return array of values
            foreach($rResult as $aRow) {
                $row = array();
                for ( $i = 0; $i < count($columns); $i++ ) {
                    if ( $columns[$i] == "version" ) {
                        // Special output formatting for 'version' column
                        $row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
                    }
                    else if ( $columns[$i] != ' ' ) {
                        $row[] = $aRow[ $columns[$i] ];
                    }
                }
                $output['aaData'][] = $row;
            }
    
            echo json_encode( $output );
        }
    }
    header('Pragma: no-cache');
    header('Cache-Control: no-store, no-cache, must-revalidate');
    // Create instance of TableData class
    $table_data = new TableData();
    ?>
    
Sign In or Register to comment.