Natural sorting Ajax Datatables in Codeigniter

Question

Struggling to figure out how to set natural sorting in AJAX Datatables using Codeigniter Active record. The field that should be sorted has, in most cases, just digits...in other cases a string, so the MySQL table field is set as VARCHAR.

I need to srt naturally the field to be displayed in Datatables.

The Active record Codeigniter query is the following.

function list_all($limit,$start,$col,$dir)
{   
    $this->rmi_db->select (" 
        $this->table_dev.id,
        $this->table_dev.fl,
        $this->table_dev.mm,
        $this->table_dev.batch,
        $this->table_dev.n,
        $this->table_dev.ditta,
        $this->table_dev.tipo,
        $this->table_dev.costruzione,
        $this->table_dev.motori,
        $this->table_dev.nc,
        $this->table_dev.serie,
        $this->table_dev.ca,
        $this->table_dev.consegna,
        $this->table_dev.matr_usaf AS usaf,
        $this->table_dev.matr_usn AS usn,
        $this->table_dev.matr_caf AS caf,
        $this->table_dev.matr_raf AS raf,
        $this->table_dev.codici,
        $this->table_dev.note,
        $this->table_dev.reg_civili,
        $this->table_dev.matricola_civ,
        $this->table_dev.prima_reg,
        $this->table_dev.n_contratto,
        $this->table_dev.data_contratto,
        $this->table_dev.importo_contratto,
        $this->table_dev.note_contratto,
        $this->table_dev.f29,
        $this->table_dev.f30,     
        ");
    $this->rmi_db->from("$this->table_dev");
    $this->rmi_db->where("$this->table_dev.mm !=", "");
    $this->rmi_db->limit($limit, $start);
    $this->rmi_db->order_by($col, $dir);
    $query = $this->rmi_db->get();

    if($query->num_rows()>0)
    {
        return $query->result(); 
    }
    else
    {
        return null;
    }
}

The mm field should be sorted naturally. I have no idea how and if it's possible to fix the issue. I tried the solution in this discussion solutions, the Bin way, but the select doesn't work properly ( got 500 server error)

Thanks a lot for any help

Aman Attari · Accepted Answer · 2020-04-27 02:35:46Z

1

Using Solution, Try below. It should work but not tested.

function list_all($limit,$start,$col,$dir)
{   
$this->rmi_db->select (" 
    $this->table_dev.id,
    $this->table_dev.fl,
    $this->table_dev.mm,
    $this->table_dev.mm, CAST($this->table_dev.mm as SIGNED) AS casted_column,//changed
    $this->table_dev.batch,
    $this->table_dev.n,
    $this->table_dev.ditta,
    $this->table_dev.tipo,
    $this->table_dev.costruzione,
    $this->table_dev.motori,
    $this->table_dev.nc,
    $this->table_dev.serie,
    $this->table_dev.ca,
    $this->table_dev.consegna,
    $this->table_dev.matr_usaf AS usaf,
    $this->table_dev.matr_usn AS usn,
    $this->table_dev.matr_caf AS caf,
    $this->table_dev.matr_raf AS raf,
    $this->table_dev.codici,
    $this->table_dev.note,
    $this->table_dev.reg_civili,
    $this->table_dev.matricola_civ,
    $this->table_dev.prima_reg,
    $this->table_dev.n_contratto,
    $this->table_dev.data_contratto,
    $this->table_dev.importo_contratto,
    $this->table_dev.note_contratto,
    $this->table_dev.f29,
    $this->table_dev.f30,     
    ");
$this->rmi_db->from("$this->table_dev");
$this->rmi_db->where("$this->table_dev.mm !=", "");
$this->rmi_db->limit($limit, $start);
$this->rmi_db->order_by($col, $dir);
$this->rmi_db->order_by('casted_column', 'ASC'); // changed
$this->rmi_db->order_by($this->table_dev.mm, 'ASC'); // changed
$query = $this->rmi_db->get(); //changed

if($query->num_rows()>0)
{
    return $query->result(); 
}
else
{
    return null;
}
}

comment if you face any issue

edited Apr 27, 2020 at 2:35

answered Apr 25, 2020 at 13:05

Aman Attari

1913 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Federico Over a year ago

Thanks a lot for your time and help Aman. The suggested code didn't fix the issue...since the mm field is not exactly sorted in natural sort...it looks like a progression in multiples of 2.

Aman Attari Over a year ago

Did you changed this line from $this->db->order_by('name', 'ASC'); to $this->db->order_by('mm', 'ASC');

Federico Over a year ago

Yep, and the query is now ordered even if in a weird way.....maybe BIN fix provide result multiplied by 2...did you see the link above? Instead 1,2,3,4,5 and so on... I get 1,2,4,8,16,32,6,128 and so on. Again thanks for all your help.

Aman Attari Over a year ago

Yes, I saw that output. Please check updated answer with Cast way !

Federico Over a year ago

Oh my....works perfect now Aman! Thanks a lot. I've just edited a bit the following line: CAST(name as SIGNED) AS casted_column, into CAST($this->table_dev.mm as SIGNED) AS casted_column, Can I ask what's the purpose of "as SIGNED"? I have learnt a lot thanks to your help. Many thanks

|

Collectives™ on Stack Overflow

Natural sorting Ajax Datatables in Codeigniter

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related