I need help understanding the editor.dependent() functionality.

Erik Skov

Description of problem: Confused by editor.dependent()

I have read this:
https://editor.datatables.net/reference/api/dependent()#Description

And this:
https://datatables.net/blog/2017-09-01

I have also found this:
https://www.blogarama.com/blogging-blogs/1336703-just-read-blog/34332879-datatable-editor-dependent-field-country-state-example-dynamic-category-subcategory

What I do not understand is how to implement it. I have several DTE projects up and running. If I can understand this, I can finish implementing several more.

We have a heirarchical structure. Let's keep it simple. Companies with several offices. An adjuster is assigned to an office. I want the user to select the company in a dropdown and have that selection limit the office dropdown to offices related to that company. These lists are stored in a mySQL database. There are indexes and a foreign key linking the tables.

Using DTE 1.7.4.

One reason for doing this is that in some cases there are just so many rows that having the column defined as below causes the page to hang in the browser. In other cases I just want to reduce the list to a reasonable number.
js

                {
                    "label": "Company:",
                    "name": "rr_office.parent_company",
                    "type": "select",
                     placeholder: "Select Company"
                },
                {
                    "label": "Office:",
                    "name": "rr_adjusters.office_id",
                    "type": "select",
                     placeholder: "Select Office"
                },

php

                Field::inst('rr_office.parent_company')
                ->options(Options::inst()
                        ->table('rr_company')
                        ->value('id')
                        ->label(['company_name', 'kind'])
                        ->render(function ( $row ) {
                            return  $row['company_name'].' - '.$row['kind'];
                        })
                )
                ->set(false),

                Field::inst('rr_adjusters.office_id')
                ->options(Options::inst()
                        ->table('rr_office, rr_company')
                        ->value('rr_office.id')
                       ->label( array ('rr_company.company_name', 'rr_office.office', 'rr_office.legacy_code'))
                        ->render(function ( $row ) {
                            return  $row['rr_company.company_name'].' - '.$row['rr_office.office'].' - '.$row['rr_office.legacy_code'];
                        })
                        ->where( function($q) {
                            $q ->where( function($r) {
                                // this is the actual inner join of the tables.
                                // You need the "false" in order to avoid "table.id" being escaped as a string.
                                $r ->where('rr_company.id', 'rr_office.parent_company', '=', false);
                                $r ->where('rr_office.active', null);
                            });
                        } )
                )
                ->validator('Validate::dbValues'),

I would like to set it up so that the column 'Office' above initially does not retrieve the possible list, but only does so after a value in the other column, Company, is selected.

1. How do I prevent the retrieve of column office on the load of the page?

I added:
editor.dependent( 'parent_company', './company-offices.php');

1. From here, https://datatables.net/blog/2017-09-01, does the following

$countries = $db
    ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
    ->fetchAll();
 
echo json_encode( [
    'options' => [
        'country' => $countries
    ]
] );

become:
(contents of ./company-offices.php)

include_once( "/rr/www/editor/php/lib/DataTables.php" );

// listener
$offices = $db
    ->select( 'office_id', ['id as value', 'office as label'], ['parent_company' => $_REQUEST['values']['parent_company']] )
    ->fetchAll();

echo json_encode( [
    'options' => [
        'office' => $offices
    ]
] );

and does it go in my main php file or its own php file?

1. What am I missing as this does not seem to work.

rf1234

I want the user to select the company in a dropdown and have that selection limit the office dropdown to offices related to that company. These lists are stored in a mySQL database. There are indexes and a foreign key linking the tables.

What you would need to do:
- Since you don't want to use the standard options you don't need an options instance on the server in PHP (The options instance in PHP can only retrieve ONE set of options for a field. That won't help you at all.)
- Dependent on the the company selection you would need to read the options from the server and update the options of your "select" field dynamically.

editor
    .dependent("rr_office.parent_company", function(val, data, callback) {
         $.ajax({
             type: "POST",
             url: 'yourUrl',
             data: {
                 parent_company: <the one that is relevant I guess>
            },
            dataType: "json",
            success: function (data) {
                 editor.field("rr_adjusters.office_id").update(data):
            }
         });
         callback({});
    })

https://editor.datatables.net/reference/api/field().update()

"data" should contain an array of objects, namely the usual label - value pairs.

On the server your script should just do this:

echo json_encode( <the array of label - value pairs you selected> );

You don't need to use datatables for this. You can read the options using your own database handler which might be easier.

Here is an example form my own coding returning options using my own db handler:

$dbh->query('SELECT DISTINCT a.dept_name AS label, a.id AS value 
               FROM ctr_govdept a
         INNER JOIN ctr_govdept_has_user_complete b ON a.id = b.ctr_govdept_id
              WHERE b.user_id = :userId
                AND b.role IN ("Principal", "Administrator", "Editor")
           ORDER BY 1 ASC');
$dbh->bind(':userId', $_SESSION['id']); 

echo json_encode( $dbh->resultsetAssoc() );

rf1234

just move the "callback" to "success" ... sorry.

editor
    .dependent("rr_office.parent_company", function(val, data, callback) {
         $.ajax({
             type: "POST",
             url: 'yourUrl',
             data: {
                 parent_company: val
            },
            dataType: "json",
            success: function (data) {
                 editor.field("rr_adjusters.office_id").update(data):
                 callback({});
            }
         });
    })

Erik Skov

Thank you for the quick response. I will try what you suggest and let you know.

rf1234

Good luck, Erik!

It took me a while to find my way using "dependent". In my opinion it is too flexible and the docs are confusing in this case. Sometimes less is more ... You'll figure it out!

@allan: sorry, but this is one of the rather few criticisms I have: "dependent" really needs to be redocumented and maybe the docs on "dependent" need to be split into several versions: "beginner", "advanced", "know it all".

allan

Yup - I agree with this point. The dependent() method can be quote complex to use which is currently reflected in the docs. I need to rework them to make it more approachable. Thanks for the feedback!

Allan

Erik Skov

rf1234. I am missing a piece. According to my sql log, the query I am generating is:

SELECT id as 'value', office as 'label' FROM rr_office WHERE parent_company IS NULL

my php file is this:

include_once( "/rr/www/editor/php/lib/DataTables.php" );

$parent_company = filter_input(INPUT_POST, 'parent_company', FILTER_SANITIZE_NUMBER_INT); // $_POST['parent_company'];

// listener
$offices = $db
    ->select( 'rr_office', ['id as value', 'office as label'], ['parent_company' => $parent_company])
        // $_REQUEST['values']['parent_company']] )
    ->fetchAll();

echo json_encode( [
    'options' => [
        'office' => $offices
    ]
] );

and here is my dependent function. The console.log() shows me I have the value.

        editor.dependent("rr_office.parent_company", function(val, data, callback) {
            console.log("ed.dep parent_company val: "+val);
            $.ajax({
                type: "POST",
                url: './company-offices.php',
                data: function ( d ) {
                    d.parent_company = val;
                },
                dataType: "json",
                success: function (data) {
                     editor.field("rr_adjusters.office_id").update(data);
                     callback({});
                }
            });
        });

So, "val" above is not getting to the php page to be used in the query. I did try it the way it was written, but that gave me NULL as well, so I tried an alternate format.

Any idea why "val" is not getting to the php file?

allan

Since you are calling field().update() yourself, you should use:

editor.field("rr_adjusters.office_id").update(data.options.office);

OR

Don't call that method yourself and do:

callback(data);

since you are have the server replying in the JSON structure that Editor can use to modify the form.

Allan

Erik Skov

I have a critical failure. I have verified that the following is NOT sending anything to my php page.

editor.dependent("rr_office.parent_company", function(val, data, callback) {
console.log("ed.dep parent_company val: "+val);
$.ajax({
type: "POST",
url: './company-offices.php',
data: function ( d ) {
d.parent_company = val;
},
...
});

I am on the verge of abandoning dependent() and doing this some other way.

rf1234

Well, you shouldn't... It should be independent of "dependent". I coded an ajax call inside a "dependent" event handler just yesterday... and it worked right away. Maybe you have a different issue?! Can you post a test case?

allan

If you can give me a link to your page I'd be happy to take a look at it and help debug this issue. I think I see the immediate problem though:

data: function ( d ) {
  d.parent_company = val;
},

jQuery's $.ajax does not accept data as a function. It doesn't need to. Try instead:

$.ajax({
  type: "POST",
  url: './company-offices.php',
  data: {
    parent_company: val
  },
  ...
});

Then in company-offices.php you should be able to access $_POST['parent_comapny'].

Allan

Erik Skov

Thank you both very much for the continued support. Apparently the
data: function ( d ) {
d.parent_company = val;
},
was the immediate problem.

I had missed that it was an actual jQuery.ajax() call as opposed to a property of the dependent function, like it is for editor() and table().

Allen, the project is on an internal corporate website, so gaining you access might be an issue. If I need more help, I will see if I can get clearance to post the entire code of all related files here.

Off to see if I can get the offices dropdown to load now that the parameter passing is working.

Erik Skov

I just found this thread and wanted to link it here for reference.
https://datatables.net/forums/discussion/comment/212433

Erik Skov

So, apparently I made this way too complicated. Using the discussion I linked immediately above, https://datatables.net/forums/discussion/comment/212433, and with the newfound confidence of making it basically work, but getting errors returned from the ajax call, I have now simplified my code to this:

        editor.dependent("rr_office.parent_company", './company-offices.php');

and

// DataTables PHP library and database connection
include_once( "/rr/www/editor/php/lib/DataTables.php" );

// listener
$offices = $db
    ->select( 'rr_office', ['id as value', 'office as label'], ['parent_company' => $_REQUEST['values']['rr_office.parent_company']] )
    ->fetchAll();

echo json_encode( [
    'options' => [
        'rr_adjusters.office_id' => $offices
    ]
] );

And it is WORKING!!!!

I am not sure why I felt I needed to pass parameters, but I did.

Allen, I would like to keep this thread open and add to it as I complete the related items.

allan

Sure - threads are only closed after around 6 months or more anyway. Great to hear you got this working.

Allan

Erik Skov

OK, I've got this working AND I made it work in 2 different DTE projects using the same single additional php page.
PHP

// DataTables PHP library and database connection
include_once( "/rr/www/editor/php/lib/DataTables.php" );

$companyId = filter_input(INPUT_POST, 'parent_company', FILTER_SANITIZE_NUMBER_INT); // $_POST['parent_company'];
$tableName = filter_input(INPUT_POST, 'dte_table'); // $_POST['dte_table'];

// listener
// get offices for a given company that are active
$offices = $db
    ->select( 'rr_office', ['id as value', 'CONCAT(office," - ",legacy_code) as label']
            , ['parent_company' => $companyId
                , 'active' => NULL ])
    ->fetchAll();

// sort the results
$keys = array_map(function($val) { return $val['label']; }, $offices);
array_multisort($keys, $offices);

echo json_encode( [
    'options' => [
        // variable table name allows reuse of this php file.
        $tableName.".office_id" => $offices
    ]
] );

JS

        editor.dependent("rr_office.parent_company", function(val, data, callback) {
            console.log("ADJ ed.dep parent_company val: "+val);
            $.ajax({
                type: "POST",
                url: '../offices/company-offices.php',
                data: {
                    dte_table: "rr_adjusters",
                    parent_company: val
                },
                dataType: 'json',
                success: function (json) {
                     callback(json);
                }
            });
        });

In order to make the php it reusable I
1. moved it to a better / more logical position in the file tree.
2. had to change the editor.dependent() call back to the expanded ajax call form so I could pass the table as a parameter.

Thank you so much to both allan and rf1234 for your help!

allan

Awesome We got there in the end!

Allan

Erik Skov

Follow up question. I have a similar DTE I am working on and have implemented this with. The problem is even with the editor.dependent() function in place the dropdown is still insanely long. I have an idea that I could add a 2nd field

        editor.add( {
            label: "Company name Starts with:",
            name: "company_prefix"
        }, "tpc_contacts.first_name");

And get the user to type in the beginning of the name of the company.

        editor.dependent("tpc_companies.parent_company", function(val, data, callback) {

            var prefix = "";
            if (typeof document.getElementById('DTE_Field_company_prefix') != "undefined"){
                var prefixObj = document.getElementById('DTE_Field_company_prefix');
                if(prefixObj != null){
                    prefix = prefixObj.value;
                    console.log("prefix: "+prefix);
                } else {
                    console.log("prefix obj: is null");
                }
            }
            
            $.ajax({
                type: "POST",
                url: '../tpcCompany/filter.parentCompany.php',
                data: {
                    dte_table: "tpc_contacts",
                    parent_company: val,
                    starts_with: prefix
                },
                dataType: 'json',
                success: function (json) {
                     callback(json);
                }
            });
        });

php code:

$parentId = filter_input(INPUT_POST, 'parent_company', FILTER_SANITIZE_NUMBER_INT); // $_POST['parent_company'];
// -1: null - needed because many companies have null as the value for their parent

$starts_with = filter_input(INPUT_POST, 'starts_with'); // $_POST['starts_with'];

$tableName = filter_input(INPUT_POST, 'dte_table'); // $_POST['dte_table'];

// listener
// get offices for a given company that are active
$companies = $db
    ->select( 'tpc_companies', ['id as value', 'CONCAT(name," - ",phone) as label']
            , ['parent_company' => ($parentId == -1 ? null : $parentId)
               , 'name' => $starts_with . '%'])          // ***HELP~~~~
    ->fetchAll();

// sort the results
$keys = array_map(function($val) { return $val['label']; }, $companies);
array_multisort($keys, $companies);

echo json_encode( [
    'options' => [
        // variable table name allows reuse of this php file.
        $tableName.".tpc_company_id" => $companies
    ]
] );

The problem is the syntax for the line labelled ***HELP. Despite a lot of searching I still need a reference on how to format this which feeds the WHERE clause so it can use the LIKE operator. The only examples I can find involve "equals".

This is the query that is being run against the DB:
SELECT id as 'value', CONCAT(name," - ",phone) as label FROM tpc_companies WHERE parent_company = '99' AND name = 'AA%'

This is what I would like to have run against the DB:
SELECT id as 'value', CONCAT(name," - ",phone) as label FROM tpc_companies WHERE parent_company = '99' AND name LIKE 'prefix%'

Erik Skov

@allan any thoughts on the syntax of different equality tests in the $db->select where clause above?

Erik Skov

I got it.

$res = $db
    ->query('select')
        ->table( 'tpc_companies')
        ->get('id as value', 'CONCAT(name," - ",phone) as label')
        ->where('parent_company', ($parentId == -1 ? null : $parentId), "=")
        ->where('name', $starts_with.'%', 'LIKE')
        ->exec();

$companies = $res->fetchAll();