Ordering data of a dependent select field

carrarachristophe

Hello,

I have a select field "vin_sousvignobles.vignoble"

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.vin_sousvignobles.php',
        table: '#vin_sousvignobles',
        fields: [
            {
                label: "Pays:",
                name: "vin_vignobles.pays",
                type: 'select',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            },
            {
                label: "Vignoble:",
                name: "vin_sousvignobles.vignoble",
                type: 'select',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            },
            {label: "Sous-vignoble:", name: "vin_sousvignobles.sousvignoble"}
        ]
    } );

which is loading ok with

Editor::inst( $db, 'vin_sousvignobles', 'sousvignoble_id' )
    ->fields(
        Field::inst( 'vin_sousvignobles.sousvignoble' )
            ->options( Options::inst()
                ->table( 'vin_sousvignobles' )
                ->value( 'sousvignoble_id' )
                ->label( 'sousvignoble' ))
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Sous-vignoble requis' )))
            ->validator( Validate::maxLen( 40 ) )
            ->validator( Validate::unique( ValidateOptions::inst()
                ->message( 'Existe déjà' ))),
        Field::inst( 'vin_sousvignobles.vignoble' )
            ->options( Options::inst()
                ->table( 'vin_vignobles' )
                ->value( 'vignoble_id' )
                ->label( 'vignoble' )
                ->order( 'vignoble' ))
            ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues() ),
        Field::inst( 'vin_vignobles.pays' )
            ->options( Options::inst()
                ->table( 'pays' )
                ->value( 'pays_id' )
                ->label( array('pays', 'pays_code') )
                ->render( function ( $row ) {
                    return $row['pays'].' ('.$row['pays_code'].')';
                } )
                ->where( function ($q) { $q->where( 'pays.vin', '1', 'LIKE' ); })
                )
            ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues() ),
        Field::inst( 'vin_vignobles.vignoble' ),
        Field::inst( 'pays.pays' ),
        Field::inst( 'pays.pays_code' )
    )
    ->leftJoin( 'vin_vignobles', 'vin_vignobles.vignoble_id', '=', 'vin_sousvignobles.vignoble' )
    ->leftJoin( 'pays', 'pays.pays_id', '=', 'vin_vignobles.pays' )
    ->process( $_POST )
    ->json();

I also managed to make it dependent from the field pays thanks to:

editor.dependent( 'vin_vignobles.pays', 'php/cascading_sousvignoble_paysvignoble.php' );

and

<?php

include_once( "lib/DataTables.php" );
 
$vin_vignobles= $db
    ->select( 'vin_vignobles', ['vignoble_id as value', 'vignoble as label'], ['pays' => $_REQUEST['values']['vin_vignobles.pays']] )
    ->fetchAll();
    
echo json_encode( [
    'options' => [
        'vin_sousvignobles.vignoble' => $vin_vignobles
    ]
] );

the problem is that now the data in the select field is no more sorted alphabetically

How can I add ORDER?

rf1234

I see you are using the "select" method of Editor's db handler. Don't know whether you can add ORDER in that one. You could check the docs to figure it out.

But you could use the "raw" method which basically allows you to use real SQL with Editor's db handler.

In this thread you see a statement that selects ordered options (label - value pairs). Just scroll down to my last comment.
https://datatables.net/forums/discussion/comment/177739

Erik Skov

I was working on this very thing. Thank you, this example helped me a lot with the general concept of editor.dependent().

In terms of sorting the results. After reading this, I thought the answer might lie in processing the php sql results array before applying json_encode(). After some trial and error and google, I came up to this:

https://stackoverflow.com/questions/3804278/php-array-sort-using-inner-val

and this:

$keys = array_map(function($val) { return $val['label']; }, $vin_vignobles);
array_multisort($keys, $vin_vignobles);

the php becomes

<?php
 
include_once( "lib/DataTables.php" );
  
$vin_vignobles= $db
    ->select( 'vin_vignobles', ['vignoble_id as value', 'vignoble as label'], ['pays' => $_REQUEST['values']['vin_vignobles.pays']] )
    ->fetchAll();

$keys = array_map(function($val) { return $val['label']; }, $vin_vignobles);
array_multisort($keys, $vin_vignobles);
     
echo json_encode( [
    'options' => [
        'vin_sousvignobles.vignoble' => $vin_vignobles
    ]
] );

You can write the json_encode() out before and after the $keys lines and compare the two results. When testing I like to write stuff to the session:

$_SESSION["aaa-results"] = json_encode( [
    'options' => [
        'vin_sousvignobles.vignoble' => $vin_vignobles
    ]
] );

$keys = array_map(function($val) { return $val['label']; }, $vin_vignobles);
array_multisort($keys, $vin_vignobles);

$_SESSION["bbb-results"] = json_encode( [
    'options' => [
        'vin_sousvignobles.vignoble' => $vin_vignobles
    ]
] );

carrarachristophe

Thanks Erik.
It solved my problem!