ORDER with TEXT column records prefixed with interger

peterbrowne

Is there a method to order a text column with records prefixed with an interger?

 ->join(
        Mjoin::inst( 'program_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'program_outcome_unit_outcome_lookup.unit_outcome_fk' )
            ->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_outcome_lookup.program_outcome_fk' )
            ->order( 'program_outcome.program_outcome asc' )
            ->fields(
                Field::inst( 'program_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'program_outcome' )
                        ->value( 'program_outcome_pk' )
                        ->label( 'program_outcome' )
                        ->render( function ( $row ) {
            return strip_tags($row['program_outcome']);
        } )
                        ->order( 'program_outcome' )
                    ),
                Field::inst( 'program_outcome' )
            )
    )

rf1234

you can pad the numbers to two digits and then you should be ok if you save the padded numbers in the database
02, 03, 19, 20, etc.

Best is to save the number and the text in two different db fields. Makes you a little more flexible - and most importantly: you don't need to pad the number for ordering.

Here is an example. "number" is the number that I pad to two digits for rendering and "label" is the text.

Field::inst( 'report.report_type_id' )
    ->options( Options::inst()
        ->table('report_type')
        ->value('id')
        ->label( array('label', 'number') )  //we need to get number into the field list otherwise SQL error with the server MySQL-Version. 
        ->render( function ( $row ) { 
            return sprintf("%02d", $row['number']) . ": " . $row['label']; 
        } )
        ->order('number asc')
    ),

If that doesn't work because you can't save the padded number strings in the database then you know the solution already ... my previous post with the "xhr" event client side:
You would need to do the padding, ordering and the removal of the padded 0 with Javascript "on xhr".