Ajax Table not Sorting

arnonrdp

Hey guys, I am facing a problem after include Ajax on my website. Sorting is not working.
Not only sorting, but other stuff too. But I am sure if I figure out what is going on, I can solve the other problems.

This is what I am doing:
index.html:

    <div class="table-responsive">
      <table id="opcoesListTable" class="table table-sm table-borderless">
        <thead>
          <tr>
            <th>Cód.<br>Ação</th>
            <th>Cód.<br>Opção</th>
            <th class="text-center">Tipo</th>
            <th class="text-center">Estilo</th>
            <th class="text-center">Vencimento</th>
            <th class="text-right">Strike</th>
            <th class="text-right">Preço<br>Opção</th>
            <th class="text-right">Negócios</th>
            <th class="text-right">Volume</th>
          </tr>
        </thead>
      </table>
    </div>

main.js:

        $(document).ready(function () {
          $(document).ready(function () {
            $("#opcoesListTable").DataTable({
              processing: true,
              serverSide: true,
              order: [],
              ajax: {
                url: baseURL+'admin/opcoesAjax/getLists/',
                type: "POST"
              },
              columnDefs: [
                {
                  targets: [0],
                  orderable: false
                }
              ]
            });
          });
        });

opcoesListarAjax.php:

    <?php
    defined('BASEPATH') OR exit('No direct script access allowed');
    
    class OpcoesAjax extends CI_Controller {
    
        public function __construct()
        {
            parent::__construct();
            if (!$this->ion_auth->logged_in()) {
                redirect('admin/login', 'refresh');
            }
    
            $this->load->model('opcoesAjax_model');
        }
    
        public function index()
        {
            
            $data['titulo'] = 'Todas Opções Ajax';
            $data['view'] = 'admin/opcoes/opcoesListarAjax';
        
            $this->load->view('admin/template/index', $data);
        }
        
        function getLists(){
            $data = $row = array();
            
            // Fetch member's records
            $opcoesData = $this->opcoesAjax_model->getRows($_POST);
            
            $i = $_POST['start'];
            
            foreach($opcoesData as $opcoes){
                $i++;
                //-----------------------------------------------------------------------------
                $dt_venc = substr($opcoes->data_venc,6,2).'/'.substr($opcoes->data_venc,4,2).'/'.substr($opcoes->data_venc,2,2);
                
                $preco_ult = number_format($opcoes->preco_ult,2, ',', '.');
                $preco_exercicio = number_format($opcoes->preco_exercicio,2, ',', '.');
                $qtde = number_format($opcoes->qtde_total,0, ',', '.');
                $volume = number_format($opcoes->volume_total,2, ',', '.');
    
                $estilo_opcao = '<img src='. base_url('public/img/europeia.svg').' class="w-20">';
                
                if ($opcoes->tipo_opcao) {
                    $estilo_opcao = '<img src='. base_url('public/img/americana.svg').' class="w-20">';
                }
                
                $tipo_merc = 'CALL';
    
                if($opcoes->tipo_mercado == 80){
                   $tipo_merc = 'PUT';
                }   
    
                //-----------------------------------------------------------------------------
                
                $data[] = array(
                    $opcoes->codigo_neg_acao, 
                    $opcoes->codigo_neg, 
                    $tipo_merc, 
                    $estilo_opcao, 
                    $dt_venc, 
                    $preco_ult,
                    $preco_exercicio,
                    $qtde,
                    $volume);
            }
            
            $output = array(
                "draw" => $_POST['draw'],
                "recordsTotal" => $this->opcoesAjax_model->countAll(),
                "recordsFiltered" => $this->opcoesAjax_model->countFiltered($_POST),
                "data" => $data,
            );
            
            // Output to JSON format
            echo json_encode($output);
        }   
        
    }

OpcoesAjax_model.php:

    <?php
    defined('BASEPATH') OR exit('No direct script access allowed');
    
    class OpcoesAjax_model extends CI_Model {
        
        function __construct() {
            // Set table name
            $this->table = 'cotacoes_opcoes_dia';
            // Set orderable column fields
            $this->column_order = array(null, 'codigo_neg_acao','codigo_neg','tipo_opcao','tipo_mercado','preco_exercicio','data_venc','preco_ult','qtde_total','volume_total');
            // Set searchable column fields
            $this->column_search = array('codigo_neg_acao','codigo_neg','tipo_opcao','tipo_mercado','preco_exercicio','data_venc','preco_ult','qtde_total','volume_total');
            // Set default order
            $this->order = array('codigo_neg' => 'asc');
        }
        
        /*
         * Fetch members data from the database
         * @param $_POST filter data based on the posted parameters
         */
        public function getRows($postData){
            $this->_get_datatables_query($postData);
            if($postData['length'] != -1){
                $this->db->limit($postData['length'], $postData['start']);
            }
            $query = $this->db->get();
            return $query->result();
        }
        
        /*
         * Count all records
         */
        public function countAll(){
            $this->db->from($this->table);
            return $this->db->count_all_results();
        }
        
        /*
         * Count records based on the filter params
         * @param $_POST filter data based on the posted parameters
         */
        public function countFiltered($postData){
            $this->_get_datatables_query($postData);
            $query = $this->db->get();
            return $query->num_rows();
        }
        
        /*
         * Perform the SQL queries needed for an server-side processing requested
         * @param $_POST filter data based on the posted parameters
         */
        private function _get_datatables_query($postData){
             
            $this->db->from($this->table);
    
            $i = 0;
            // loop searchable columns 
            foreach($this->column_search as $item){
                // if datatable send POST for search
                if($postData['search']['value']){
                    // first loop
                    if($i===0){
                        // open bracket
                        $this->db->group_start();
                        $this->db->like($item, $postData['search']['value']);
                    }else{
                        $this->db->or_like($item, $postData['search']['value']);
                    }
                    
                    // last loop
                    if(count($this->column_search) - 1 == $i){
                        // close bracket
                        $this->db->group_end();
                    }
                }
                $i++;
            }
             
            if(isset($postData['order'])){
                $this->db->order_by($this->column_order[$postData['order']['0']['column']], $postData['order']['0']['dir']);
            }else if(isset($this->order)){
                $order = $this->order;
                $this->db->order_by(key($order), $order[key($order)]);
            }
        }
    
    }

Anyone know what I am doing wrong?

allan

Does whatever SQL abstraction layer you are using allow you to print out the SQL that is being generated? If so, then I'd suggest printing out the SQL at line 26 to see what it being built, and thus why it isn't sorting. That would be the starting point to debug your code.

Allan