ORDER with TEXT column records prefixed with interger

ORDER with TEXT column records prefixed with interger

peterbrownepeterbrowne Posts: 103Questions: 23Answers: 0

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' )
            )
    )

Answers

  • rf1234rf1234 Posts: 1,180Questions: 48Answers: 161
    edited March 18

    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".

Sign In or Register to comment.