Ordering data of a dependent select field

Ordering data of a dependent select field

carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2
edited September 2022 in Editor

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?

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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 SkovErik Skov Posts: 33Questions: 6Answers: 2
    Answer ✓

    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
        ]
    ] );
    
  • carrarachristophecarrarachristophe Posts: 97Questions: 22Answers: 2

    Thanks Erik.
    It solved my problem!

Sign In or Register to comment.