Ordering dynamic dropdown list in Editor form

reefoid

Hi

I've managed to get Editor working using joined tables. I have a dropdown list that is generated from values in the DB, but they are displayed in the order they are stored in the DB. How do I go about getting these values to display in alphabetical order? I've tried sorting the array $out['clients'] but it doesn't seem to have any affect.

[code]
<?php

// DataTables PHP library
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'projects' )
->field(
Field::inst( 'id' ),
Field::inst( 'pro_name' )->validator( 'Validate::required' ),
Field::inst( 'pro_code' )->validator( 'Validate::required' ),
Field::inst( 'pro_desc' )->validator( 'Validate::required' ),
Field::inst( 'pro_client' ),
Field::inst( 'pro_start' )
->getFormatter( 'Format::date_sql_to_format', 'd/m/Y' )
->setFormatter( 'Format::date_format_to_sql', 'd/m/Y' ),
Field::inst( 'pro_end' )
->getFormatter( 'Format::date_sql_to_format', 'd/m/Y' )
->setFormatter( 'Format::date_format_to_sql', 'd/m/Y' )
)
->join(
Join::inst( 'clients', 'object' )
->join(
array( 'id', 'proj_id' ),
array( 'id', 'cli_id' ),
'clients_projects'
)
->field(
Field::inst( 'id' )->validator( 'Validate::required' ),
Field::inst( 'cli_name' )
)
);

// The "process" method will handle data get, create, edit and delete
// requests from the client
$out = $editor
->process($_POST)
->data();


// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client
if ( !isset($_POST['action']) ) {
// Get client details
$out['clients'] = $db
->query( 'select', 'clients' )
->get( 'id as value, cli_name as label' )
->exec()
->fetchAll();
}

// Send it back to the client
echo json_encode( $out );
[/code]

[code]
var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
// Create the form
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/projects.php",
"domTable": "#example",
"fields": [ {
"label": "Project:",
"name": "pro_name"
}, {
"label": "Project Code:",
"name": "pro_code"
}, {
"label": "Description:",
"name": "pro_desc"
}, {
"label": "Client:",
"name": "clients.id",
"type": "select"
}, {
"label": "Start Date:",
"name": "pro_start",
"type": "date",
"dateFormat": 'dd/mm/yy'
}, {
"label": "End Date:",
"name": "pro_end",
"type": "date",
"dateFormat": 'dd/mm/yy'
}
]
} );

// When the editor is opened, bind a key listener to the window
editor.on('onOpen', function () {
$(document).bind('keyup', function (e) {
if ( e.keyCode === 13 ) {
// On return, submit the form
editor.submit();
}
else if ( e.keyCode === 27 ) {
// On escape, close the form
editor.close();
}
} );
} );

// New record
$('a.editor_create').on('click', function (e) {
e.preventDefault();

editor.create(
'Create New Client',
{ "label": "Add", "fn": function () { editor.submit() } }
);
} );

// Edit record
$('#example').on('click', 'a.editor_edit', function (e) {
e.preventDefault();

editor.edit(
$(this).parents('tr')[0],
'Edit record',
{ "label": "Update", "fn": function () { editor.submit() } }
);
} );

// Delete a record (without asking a user for confirmation)
$('#example').on('click', 'a.editor_remove', function (e) {
e.preventDefault();

editor.remove( $(this).parents('tr')[0], '123', false, false );
editor.submit();
} );

// DataTables init
$('#example').dataTable( {
"sDom": "<'row-fluid'<'span4'f><'span6 pull-right'p>r>t<'row-fluid'<'span6'i><'span6'p>>",
"sAjaxSource": "php/projects.php",
"sPaginationType": "bootstrap", // pagination type
"aaSorting": [[0, "asc" ]], // define column to sort on
"aoColumns": [
{ "mData": "pro_name" },
{ "mData": "pro_code" },
{ "mData": "pro_desc" },
{ "mData": "clients.cli_name" },
{ "mData": "pro_start" },
{ "mData": "pro_end" },
{
"mData": null,
"sDefaultContent": 'Edit',
"bSortable": false
}
],
"fnInitComplete": function ( settings, json ) {
// Set the allowed values for the select field based on
// what is available in the database
editor.field('clients.id').update( json.clients );
}
} );
} );
[/code]

Thanks

allan

Add an order to the get form the database for the clients:

[code]
$out['clients'] = $db
->query( 'select', 'clients' )
->get( 'id as value, cli_name as label' )
->order( 'label ASC' )
->exec()
->fetchAll();
[/code]

And that should do it. Another option is to do it client side in your fnInitComplete, but this is probably the most efficient.

Allan

reefoid

Excellent, thanks. I had actually tried this earlier, but had not included the ASC argument.

Cheers.