Multiple order doesn't work

pippuccio76

Hi , i have a table with 2 culumn order , one is type hour (european) one a number this is the link :

https://realizzazionesoftware.eu/software.fogliodiviaggio.it/index.php/test/lista_test

The date are ordered correctly but the culum NumFoglio is not ordered in desc Way.

F.E. in second page on date 29-08-2023 there are three record first il 547 than 546 and 548 (this must be the first)

allan

The time column contains two different formats for the times - i.e. 9,30 and 9:30.

In addition to that, DataTables doesn't detect HH:MM automatically. You'd need to delare the format as shown in this example.

Allan

kthorngren

Since you are using server side processing (serverSide: true) the server script is responsible for the table sorting. Are you using a Datatables supplied script?

The data query in the server script will need debugging. Could be the column has a mixture of numeric data and text data.

Kevin

allan

I missed that - nice spotting Kevin!

Allan

pippuccio76

hi , this is the server side code :

  public function lista_ajax(){

        $postData = $_POST;
        $dtpostData = $postData['data'];
        $response = array();

        ## Read value
        $draw = $dtpostData['draw'];
        $start = $dtpostData['start'];
        $rowperpage = $dtpostData['length']; // Rows display per page
        $columnIndex = $dtpostData['order'][0]['column']; // Column index
        $columnname = $dtpostData['columns'][$columnIndex]['data']; // Column 
        $columnSortOrder = $dtpostData['order'][0]['dir']; // asc or desc
        $searchValue = $dtpostData['search']['value']; // Search value
        $where='';


        $totalRecords   = $this->db->count_all('foglio_viaggio');

        $recordwithFilter = $this->db->select('
                    foglio_viaggio.num_foglio,
                    foglio_viaggio.data,
                    foglio_viaggio.ore_inizio_servizio,

            ')
            ->from('foglio_viaggio')
            ->where('foglio_viaggio.user_id',9)
            ->group_start() // apro una parentesi 
                    ->or_like('foglio_viaggio.num_foglio',$searchValue)
                    ->or_like('foglio_viaggio.data',$searchValue)
                    ->or_like('foglio_viaggio.ore_inizio_servizio',$searchValue)                    
            ->group_end() // chiudo la parentesi
            ->order_by($columnname,$columnSortOrder)
            ->get()
            ;

            $totalRecordwithFilter= count($recordwithFilter->result());



            $records = $this->db->select('
                    foglio_viaggio.num_foglio,
                    foglio_viaggio.data,
                    foglio_viaggio.ore_inizio_servizio,
            ')
            ->from('foglio_viaggio')           
            ->where('foglio_viaggio.user_id',9)
            ->group_start() // apro una parentesi               
                    ->or_like('foglio_viaggio.num_foglio',$searchValue)
                    ->or_like('foglio_viaggio.data',$searchValue)
                    ->or_like('foglio_viaggio.ore_inizio_servizio',$searchValue)                    
            ->group_end() // chiudo la parentesi
            ->order_by($columnname,$columnSortOrder)
            ->limit($rowperpage, $start)
            ->get()
            ;



        $data = array();

        //echo $this->db->last_query();
        //die();

        foreach($records->result() as $record ){


            $data[] = array( 
                "num_foglio"=>$record->num_foglio,
                "data"=>date_local_format_from_iso($record->data),
                "ore_inizio_servizio"=>$record->ore_inizio_servizio,

                ); 
        }

        ## Response


        $response = array(
            "draw" => intval($draw),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalRecordwithFilter,
            "aaData" => $data,
            "token" => $this->security->get_csrf_hash() // New token hash
        );


        //echo   $macchina_model->getLastQuery();
        //die();

         echo json_encode($response);

    }//finer lista AJAX

Added $.fn.dataTable.moment( 'HH:mm');

Now the order on second page (29-08-2023) are 546-548-547

allan

Added $.fn.dataTable.moment( 'HH:mm');

You can drop that if you are using server-side processing. It won't do anything.

I would suggest that the way to debug this is to have your server-side script dump the SQL that it is generating, so you can debug the ordering statements it is using.

Allan

allan

Worth noting that your code appears to only order by a single column ($dtpostData['order'][0]). It doesn't take account of the fact that order might be an array greater than length 1 (also note that it could be length 0!). You should use a loop there.

Allan

pippuccio76

I must change code only server side or both server side and client side ? Where i can find php server side code for multple order ?

pippuccio76

Change my server side code :

    public function lista_ajax(){




        $postData = $_POST;
        $dtpostData = $postData['data'];
        $response = array();

        ## Read value
        $draw = $dtpostData['draw'];
        $start = $dtpostData['start'];
        $rowperpage = $dtpostData['length']; // Rows display per page


        $sorting = '';
        foreach($dtpostData['order'] as $index=>$v){

            //log_message('error', print_r($v));

            $sorting .= $dtpostData['columns'][$v['column']]['data'].' '.$v['dir'].' ';

            if ($index !== array_key_last($dtpostData['order'] )){

                $sorting .=',';

            }
        }


        $searchValue = $dtpostData['search']['value']; // Search value
        $where='';


        //log_message('error', print_r($dtpostData));


        $totalRecords   = $this->db->count_all('foglio_viaggio');

        $recordwithFilter = $this->db->select('
                    foglio_viaggio.num_foglio,
                    foglio_viaggio.data,
                    foglio_viaggio.ore_inizio_servizio,

            ')
            ->from('foglio_viaggio')
            ->where('foglio_viaggio.user_id',9)
            ->group_start() // apro una parentesi 
                    ->or_like('foglio_viaggio.num_foglio',$searchValue)
                    ->or_like('foglio_viaggio.data',$searchValue)
                    ->or_like('foglio_viaggio.ore_inizio_servizio',$searchValue)                    
            ->group_end() // chiudo la parentesi
            ->order_by($sorting)
            ->get()
            ;

            $totalRecordwithFilter= count($recordwithFilter->result());



            $records = $this->db->select('
                    foglio_viaggio.num_foglio,
                    foglio_viaggio.data,
                    foglio_viaggio.ore_inizio_servizio,
            ')
            ->from('foglio_viaggio')           
            ->where('foglio_viaggio.user_id',9)
            ->group_start() // apro una parentesi               
                    ->or_like('foglio_viaggio.num_foglio',$searchValue)
                    ->or_like('foglio_viaggio.data',$searchValue)
                    ->or_like('foglio_viaggio.ore_inizio_servizio',$searchValue)                    
            ->group_end() // chiudo la parentesi
            ->order_by($sorting)
            ->limit($rowperpage, $start)
            ->get()
            ;

        log_message('error', $this->db->last_query());

        $data = array();

        //echo $this->db->last_query();
        //die();

        foreach($records->result() as $record ){


            $data[] = array( 
                "num_foglio"=>$record->num_foglio,
                "data"=>date_local_format_from_iso($record->data),
                "ore_inizio_servizio"=>$record->ore_inizio_servizio,

                ); 
        }

        ## Response


        $response = array(
            "draw" => intval($draw),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalRecordwithFilter,
            "aaData" => $data,
            "token" => $this->security->get_csrf_hash() // New token hash
        );


        //echo   $macchina_model->getLastQuery();
        //die();

         echo json_encode($response);

    }//fine lista AJAX

Now it work