Cascading lists in Editor: please explain

Cascading lists in Editor: please explain

ostmalostmal Posts: 102Questions: 33Answers: 0

Hello! I recently started studying DataTables. Stopped with dependent fields. I looked at a lot of examples and still a lot is unclear.

There is a classic example:
https://datatables.net/blog/2017-09-01

It is often pointed at. And it is important for me to understand.
It would be nice to decipher it a bit:
1. I don't understand: how many tables are there in the Database? As far as I understand 4:
Table 1- for filling in
Table 2 - " Continents»
Table 3 - " Countries»
Table 4 – " id_Сontinent-id_ Country»
2. I really want to see the "JavaScript" code
3. editor.dependent ('continent', '/ api/countries' );
the Second argument «/ api/countries» what is it?
4. Do I understand correctly that in this example there are actually two Server script (PHP files)? I'd like to see both.
I'm sorry, I'm still at the beginner level and I need your help.

«1

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    1. There would be three tables: the person, then countries, and the continents
    2. Right cick on the page and select "View source" - you can see it there.
    3. That's the URL for the ajax call - see usage for dependent()
    4. Yep, exactly. Similar scripts are in the download package, so they can be used for reference

    Hope that helps,

    Colin

  • ostmalostmal Posts: 102Questions: 33Answers: 0

    Thank you very much, Colin, for your prompt support. I would like to clarify the points.
    1. I understand that the "countries" Table has an id – continents field to organize the "continent-country" match.
    2. I looked at the code, thank you.
    3. I have read the "dependent ()" documentation many times. Some things are not clear to me, perhaps my questions will seem primitive. Your code first calls (ajax) " url: /media/blog/2017-09-01/data . And then, by independent (): /media/blog/2017-09-01/countries . I don't quite understand what PHP files should be called? Or why is there no file name?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    If you call it by hand, https://datatables.net/media/blog/2017-09-01/countries, you'll see that this is the end point - you can either have .php on the end or not. That is the URL that's being called by the script - apart from when you run it by hand, the data part isn't being submitted (see "submit data" for that part of the dependent() documentation).

    Colin

  • ostmalostmal Posts: 102Questions: 33Answers: 0

    Thanks for the answer.
    I'm sorry, I didn't quite understand. If we consider Your example:
    https://datatables.net/blog/2017-09-01
    There, the construction is used to respond to changes in the "Continent" field:
    editor.dependent( 'continent', '/api/countries' );
    Below, in this example, you have shown options for the server code that should handle this request. In particular, the version of PHP:
    include_once( $_SERVER['DOCUMENT_ROOT']."/php/DataTables.php" );

    include_once( $_SERVER['DOCUMENT_ROOT']."/php/DataTables.php" );
     
    $countries = $db
        ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
        ->fetchAll();
     
    echo json_encode( [
        'options' => [
            'country' => $countries
        ]
    ] );
    

    Question: what should I call this script and where should I put it so that it is called?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Question: what should I call this script and where should I put it so that it is called?

    In the example above it would be called countries.php (with the web-server setup not to require the .php extension) and in the api directory of the web-server's root.

    So really, you can put it anywhere you want in your web-server's directory structure and call the file what you like - you just need to update the dependent() call to make sure it knows where the file is - e.g. if you put it into {web-root}/admin/scripts/options.php you'd update the dependent() call to be:

    editor.dependent( 'fieldName', '/admin/scripts/options.php' );
    

    Allan

  • ostmalostmal Posts: 102Questions: 33Answers: 0

    Thanks. I was confused by the lack of an extension for the file. I understood.

  • ostmalostmal Posts: 102Questions: 33Answers: 0

    I'm sorry to bother you. I'm still learning, and I'm not getting it right yet.
    For training, I tried to reproduce the Continents – Countries model, as here:
    https://datatables.net/blog/2017-09-01
    I have 3 tables:

    person:
    *id
    *name (varchar)
    *continent (int)
    *country (int)
    continents:
    *id (int)
    *name (varchar)
    countries:
    *id
    *id_continents (int)
    *name (varchar)

    JS:

    (function($){
    
    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor( {
            ajax: '/abc/w_lesson_datatable/dt-10/php/table.dt_10.php',
            table: '#dt_10',
            fields: [
                {
                    "label": "Name:",
                    "name": "person.name"
                },{
                    "label": "Continent:",
                    "name": "person.continent",
                    type: "select",
                    placeholder: "Select continent"
                },{
                    "label": "Country:",
                    "name": "person.country",
                    type: "select",
                    placeholder: "Select country"
                }
            ]
        } );
    
        editor.dependent( 'person.continent', '/abc/w_lesson_datatable/dt-10/php/dependent_continent_country.php' );
    
        var table = $('#dt_10').DataTable( {
            dom: 'Bfrtip',
            ajax: '/abc/w_lesson_datatable/dt-10/php/table.dt_10.php',
            columns: [
                {"data": "person.name"},
                {"data": "continents.name"},
                {"data": "countries.name"}
            ],
            select: true,
            lengthChange: false,
            buttons: [
                { extend: 'create', editor: editor },
                { extend: 'edit',   editor: editor },
                { extend: 'remove', editor: editor }
            ]
        } );
    } );
    
    }(jQuery));
    

    PHP:

    <?php
    
    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    
    
    Editor::inst( $db, 'person')
        ->field(
        Field::inst( 'person.name' ),
    
        // field "continent"
        Field::inst( 'person.continent' )
            ->options( Options::inst()
                ->table( 'continents' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'continents.name' ),
    
        // field "country"
        Field::inst( 'person.country' )
            ->options( Options::inst()
                ->table( 'countries' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'countries.name' )
    
        )
        ->leftJoin( 'continents', 'continents.id', '=', 'person.continent' )
        ->leftJoin( 'countries', 'countries.id', '=', 'person.country' )
    
        ->process( $_POST )
        ->json();
    
    

    PHP (for dependent())

    <?php
    
    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    
    
    // DataTables PHP library and database connection
    include_once( "lib/DataTables.php" );
    
    
        $countries = $db
            ->select( 'countries', ['id as value', 'name as label'], ['continents' => $_REQUEST['values']['id_continent']] )
            ->fetchAll();
    
        echo json_encode( [
            'options' => [
                'country' => $countries
            ]
        ] );
    

    The table displays great! Editor-ок, but dependent(), cascading fields - doesn't work!
    I made a mistake somewhere.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Heh - I loose count of how many mistakes I make each day...!

    Are you getting any error messages? Something that might guide us in what is going wrong?

    Thanks,
    Allan

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Hello,

    Could you please post this
    https://datatables.net/blog/2017-09-01
    example in the datatables editor example section because I think it is very important.

    Thanks in advance

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Do you mean you want it in the demo package? I’m not entirely clear how that is more useful than the blog post?

    Allan

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Hi,

    Thanks for reply.
    No, I meant that cascade list is important and must be in the example section because the one in the blog isn't clear enough at least for me.

    Thanks again

  • SarbastSarbast Posts: 85Questions: 0Answers: 0
    edited January 2021

    Moreover, the dependent code is not clear

    ->select( 'countries', ['id as value', 'name as label'], ['?' => $_REQUEST['values?']['?']] )

    could you please explain this query in details and what must I put instaed of "?" symbol.

    Thanks

  • SarbastSarbast Posts: 85Questions: 0Answers: 0
    edited January 2021

    My dependent code:

    <?php
    
    include_once( "../../lib/DataTables.php" );
     
        $countries = $db
            ->select( 'countries', ['id as value', 'name as label'], ['id_continents' => $_REQUEST['values']['id_continents']] )
            ->fetchAll();
     
        echo json_encode( [
            'options' => [
                'country' => $countries
            ]
        ] );
    

    And this output:

    Notice: Undefined index: values in C:\xampp\htdocs\dte\examples\advanced\cascadedep.php on line 12

    Notice: Trying to access array offset on value of type null in C:\xampp\htdocs\dte\examples\advanced\cascadedep.php on line 12
    {"options":{"country":[]}}

    And Dependent does not work.
    when I block dependent in the client side file the editor work fine

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Hello,

    I tried alot with cascade list example, but it does not work so could you please give me the database tables' details, server side code, client side and dependent code as well.

    Thanks in advance

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Thanks for the suggestion of providing this in the examples section - I'll look at getting that done.

    In the mean time - what does the server return to the Ajax request if you add:

    print_r( $_REQUEST );
    

    ?

    What you have above looks okay to me and basically matches what the site is running, but something else is going on, and I'm not sure what - we'll need to debug it to get to the bottom of what is going on.

    Allan

  • windbillwindbill Posts: 6Questions: 0Answers: 0

    Sarbast - crazy thought but have you tried replacing [] with array() ?
    I also found that i had to put this in my dependent file:
    header('Content-Type: application/json');

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Thanks Allan,

    The output of this statement "print_r( $_REQUEST );" is empty array "Array ( )".

    My suggestion is to post the solution in the example section to benefit from it like other examples.

    Thanks again

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Thanks windbill,

    I tried your thought, but I got an error.

    Have you solve it or not yet?

  • windbillwindbill Posts: 6Questions: 0Answers: 0

    Yes i did solve it but i can't post my code here - it's essentially the same though. This is what I did to help solve in Chrome:
    -ctrl_shift+i (bring up inspect page)
    -select 'Network' from the top row
    -filter on 'XHR'
    Refresh the page and you should see the call to your dependent.php page followed by a question mark, your dependent field and its value
    Are you seeing that ?
    If you click on that it will show you the header, preview and response.
    I found it helpful to pull the url out of the header and enter that into the browser. You can modify it to see if the problem is with what's being sent to the server.
    Just a thought but make sure you're passing the right values.
    I found putting alert() in the js fetch part of dependent helpful too to ensure you're getting the values expected.

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Could you please send me your code as a message?

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Thanks alot
    windbill and allan,
    Finally I solve it and it works perfect.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    What did you do to resolve it? If $_REQUEST was empty, either the PHP server is misconfigured or the browser isn’t sending any data to the server (query params or request body).

    Allan

  • SarbastSarbast Posts: 85Questions: 0Answers: 0

    Hi Allan,

    I changed a little bit in the dependent file. Today I will post my DB schema and dependent code.

  • SarbastSarbast Posts: 85Questions: 0Answers: 0
    edited January 2021

    Hello all,

    Here my solution and it works perfect.
    First my DB schema:
    Table 1:
    Name: students
    Fields: id, name, college_id and dept_id
    Table 2:
    Name: colleges
    Fields: id and name
    Table 3:
    Name: depts
    Fields: id, name and college_id
    Second my dependent code:

    <?php
    // DataTables PHP library and database connection
    include_once( "../lib/DataTables.php" );
     
    $depts = $db
        ->select( 'depts', ['id as value', 'name as label'], ['college_id' => $_REQUEST['values']['students.college_id']] )
        ->fetchAll();
     
    echo json_encode( [
        'options' => [
            'students.dept_id' => $depts
        ]
    ] );
    

    Note: For client and server code are the same as the "ostmal" post it above and thanks for him. Also, you can use any of the example in the editor example section as will.

    thanks allan and windbill.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Glad all sorted,

    Colin

  • Helpdesk LionesaHelpdesk Lionesa Posts: 23Questions: 4Answers: 1

    Hello,

    I would like to know how to add a new where clause to this select

    ->select( 'countries', ['id as value', 'name as label'], ['?' => $_REQUEST['values?']['?']] )

    Example: "Status != Inactive"

    Thanks

  • Alexandr45Alexandr45 Posts: 30Questions: 1Answers: 3

    I did like this:

    $countries = $db
    ->select( 'Cars', ['id as value', 'concat(Cars_name, " " ,Cars_gnomer) as label'], ['Cars_Branches' => $_REQUEST['values']['DataWork.dw_branches'], 'Cars_equipped' => 0] )
    ->fetchAll();

    echo json_encode( [
    'options' => [
    'DataWork.dw_car_id' => $countries
    ]
    ] );

  • Helpdesk LionesaHelpdesk Lionesa Posts: 23Questions: 4Answers: 1

    Thanks alot Alexandr45 ,
    Finally I solve it and it works perfect.

  • Helpdesk LionesaHelpdesk Lionesa Posts: 23Questions: 4Answers: 1
    edited May 2021

    Hello everyone,

    Need help please.
    I have a cascading lists function working fully, but it returns the values ​​ordered by the table index. I need to sort them alphabetically. It's possible? Can someone help me please?

    <?php
    error_reporting(E_ALL);
    ini_set('display_errors', '1');
    include_once( "lib/DataTables.php" );

    if ($_REQUEST['values']["tlmk_vendas.tipo_item_id"] != "Tipo de item"){
    $grupo_item = $db
    ->select( 'tlmk_grupo_item', ['grupo_item_id as value', 'grupo_item as label'], ['tipo_item_id' => $_REQUEST['values']["tlmk_vendas.tipo_item_id"], 'status' => 'Ativo' ] )
    ->fetchAll();
    echo json_encode( [
    'options' => [
    'tlmk_vendas.grupo_item_id' => $grupo_item
    ]
    ] );
    }

    Thanks!
    João

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Sure, you can pass a fourth parameter to the $db->select() which will define the ordering. See the API docs here.

    Allan

This discussion has been closed.