How do I sort and alphanumeric field in AgileToolkit?

Question

I have a small app and I need to fix a couple of issues:

1. Need to set a field with a default sort order. This is the code I have right now

       $crud->grid->getColumn('JOB_NO')->makeSortable();
   

   The grid displays the sort icon but I want to set the default sort for this field to be descending.

2. The JOB_NO field contains alphanumeric job numbers: N999, N1000, N1001 etc...

The sort is working however it's putting N999 at the top and N1000 and N1001 are not even there only to be found several pages later in the pagination. How can I set the sort to make sure that higher job numbers stay at the top? Basically ignore the first letter maybe? Thanks

Answer 1

1) There are two options how to do this.

First option is to add sorting to Model and not directly to grid.

Then you can use:

$model->addField('foo')->sortable(true);
$model->setOrder('foo','desc');

Second option (if you want or have to do this at grid level):

$grid->getColumn('foo')->makeSortable('-');

// or even (not sure, didn't test this)
$grid->makeSortable('-foo');

2) Not sure how exactly to do this, but you can try one of the following:

• create new calculated field in model and calculate it like SUBSTR(JOB_NO,1) - strip first char. Then order by this field.
• save only numeric part of that code in database and then add "N" before number only on View level (grid column formater for example).
• note that you can also do like this somehow: SELECT job_no FROM jobs ORDER BY length(job_no),job_no

Answer 2

I would suggest you to have a separate field for sorting.

First create field "foo_sort" in your database and inside model. Set it as a system field, so that does not appear in the UI by default:

$model->addField('foo_sort')->system(true);
$model->setOrder('foo_sort');

Then create a hook which will calculate the value of the field before model is saved:

$model->addHook('beforeSave',function($m){
    $m['foo_sort']=substr(0,1,$m['foo']);
});

You don't need to do anything else, perhaps manually update your existing records in the database. This method can be used in many other scenarios for maintaining sort fields such as stripping out HTML out of full-text search data.