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-27T02:35:46.483

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 '20 at 02:35

answered Apr 25 '20 at 13:05

Aman Attari

181
3
12

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. You can see the result here: https://gavs.it/rmidev – Federico Apr 25 '20 at 14:20
Did you changed this line from $this->db->order_by('name', 'ASC'); to $this->db->order_by('mm', 'ASC'); – Aman Attari Apr 25 '20 at 14:49
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. – Federico Apr 25 '20 at 17:37
Yes, I saw that output. Please check updated answer with Cast way ! – Aman Attari Apr 26 '20 at 02:21
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 – Federico Apr 26 '20 at 07:14
Welcome-@Federico, SIGNED - it cast mm value in signed (negative and positive numbers) for example signed byte represent -128 to 127 – Aman Attari Apr 26 '20 at 13:15

Natural sorting Ajax Datatables in Codeigniter

1 Answers1