Options - Sort Help

cbookout

I am using Options to populate a drop down box with names of people. I need the names to come back in alphabetical order so they are easy to find in the dropdown. Below is my code. If anyone can help, I would appreciate it:

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'jos_tablea', 'log_id' )
    ->fields(
        Field::inst( 'jos_tablea.log_details' )->validator( 'Validate::notEmpty' ),
                Field::inst( 'jos_tablea.log_teammember' )                        
                        ->options( 'jos_tableb', 'teammember_id', array('first_name', 'last_name'), function ($q) {
                                      $q->where( 'tablec_user_id', $_SESSION['userid'])
                                          ->order( 'first_name ASC');
                        }),
        Field::inst( 'jos_tablea.log_date' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
                Field::inst( 'jos_tableb.last_contact_date' )->setValue(date('c'))
            ->getFormatter( 'Format::date_sql_to_format', 'jS F Y' )
    )
    ->leftJoin( 'jos_tableb',   'jos_tableb.teammember_id', '=', 'jos_tablea.log_teammember' )
    ->leftJoin( 'jos_tablec',   'jos_tablec.user_id',   '=', 'jos_tablea.log_owner' )
    ->process( $_POST )
    ->json();

I am using order, but they are not coming back alphabetically. It appears to be random in the dropdown.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Apezdr

This is what I'm using.

Field::inst( 'DataCollection.tasktypeid' )
            ->options( 'TaskType', 'id', array('TaskTitle','TaskType','UserIDAssociatedTo','UserGroupVisiblity'), function($q){
                $q->where("find_in_set('" . $_SESSION['usrID'] . "', cast(`UserIDAssociatedTo` as char)) > 0 OR find_in_set('" . $_SESSION['loggedinUserGroup'] . "', cast(`UserGroupVisiblity` as char)) > 0 OR find_in_set('0', cast(`UserIDAssociatedTo` as char)) > 0 AND `UserGroupVisiblity`");
            }, function($row){
                    
                return $row['TaskTitle'];
            } ),

I think you could adjust the where to say this instead.

$q->where( 'tablec_user_id = ' .  $_SESSION['userid'] . ' ORDER BY `TABLENAME`.`tablec_user_id` ASC') 

I think there is another approach that could be used but I'm not familiar with other options.

cbookout

Thanks for the help, I will give it a try!

Apezdr

No problem look forward to your results.

allan

In the Editor 1.5.x libraries, what happens is that they query the database to get the information, then render the labels (in the case above that is concatenating first_name and last_name, space separated). Then (outside the SQL) it will sort based on the rendered labels. It should string sort ascending if strings are found, and number sort ascending if numbers are found. An "ORDER BY" clause will have no effect on this since the sorting in done in PHP rather than SQL.

Long story short, what you have above looks like it should work! Could you show us the JSON being returned by the server please?

In the upcoming 1.6 libraries you will be able to specify an order to be used in SQL.

Allan