Cascading lists in Editor: please explain

ostmal

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.

colin

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

ostmal

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?

colin

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

ostmal

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?

allan

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

ostmal

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

ostmal

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.

allan

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

Sarbast

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

allan

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

Sarbast

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

Sarbast

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

Sarbast

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

Sarbast

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

allan

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

windbill

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');

Sarbast

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

Sarbast

Thanks windbill,

I tried your thought, but I got an error.

Have you solve it or not yet?

windbill

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.

Sarbast

Could you please send me your code as a message?

Sarbast

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

allan

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

Sarbast

Hi Allan,

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

Sarbast

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.

colin

Glad all sorted,

Colin

Helpdesk Lionesa

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

Alexandr45

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 Lionesa

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

Helpdesk Lionesa

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

allan

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

Allan