cascadingLists - Uncaught Unknown field name

carrarachristophe

Hello,
I am trying to apply this example to my needs.
I have 3 tables:
1. bibliotheque_editeurs, containing: editeur_id, editeur
2. bibliotheque_collections, containing: collection_id, collection, editeur
3. bibliotheque_oeuvres, containing: oeuvre_id, oeuvre , editeur, collection (with collection dropdown dependend from the editeur one)

As per the example, I added the following to my js file:

  editor.dependent( 'bibliotheque_oeuvres.editeur', 'php/table.bibliotheque_collections.php' );

but I am getting the following message:
"Uncaught Unknown field name - bibliotheque_collections.editeur"
the equivalent in the example would be:
"Uncaught Unknown field name - country.continent"

I don't understand because I don't find such reference in the example.
Additionally, my php file seeems correct, with both leftjoin. (at least it works fine without the "editor.dependent" reference)

Editor::inst( $db, 'bibliotheque_oeuvres', 'oeuvre_id' )
    ->fields(
        Field::inst( 'bibliotheque_oeuvres.oeuvre_id' ),
        Field::inst( 'bibliotheque_oeuvres.editeur' )
            ->options( Options::inst()
                ->table( 'bibliotheque_editeurs' )
                ->value( 'editeur_id' )
                ->label( 'editeur' ))
            ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues() ),
        Field::inst( 'bibliotheque_oeuvres.collection' )
            ->options( Options::inst()
                ->table( 'bibliotheque_collections' )
                ->value( 'collection_id' )
                ->label( 'collection' ))
            ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues() ),
    Field::inst( 'bibliotheque_editeurs.editeur' ),
    Field::inst( 'bibliotheque_collections.collection' )
    )
    ->leftJoin( 'bibliotheque_editeurs', 'bibliotheque_editeurs.editeur_id', '=', 'bibliotheque_oeuvres.editeur' )
    ->leftJoin( 'bibliotheque_collections', 'bibliotheque_collections.collection_id', '=', 'bibliotheque_oeuvres.collection' )
    ->process( $_POST )
    ->json();

In the example, we are calling '../php/countries.php', which I don't have access to.
the equivalent in my case is 'table.bibliotheque_collections.php':

Editor::inst( $db, 'bibliotheque_collections', 'collection_id' )
    ->fields(
        Field::inst( 'bibliotheque_collections.collection' ),
        Field::inst( 'bibliotheque_collections.editeur' )
            ->options( Options::inst()
                ->table( 'bibliotheque_editeurs' )
                ->value( 'editeur_id' )
                ->label( 'editeur' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'bibliotheque_editeurs.editeur' )
    )
    ->leftJoin( 'bibliotheque_editeurs', 'bibliotheque_editeurs.editeur_id', '=', 'bibliotheque_collections.editeur' )
    ->process( $_POST )
    ->json();

Any any of what I am missing?

kthorngren

"Uncaught Unknown field name - bibliotheque_collections.editeur"

That suggests you don't have the field bibliotheque_collections.editeur defined in your Javascript Editor instance assigned to the variable editor. PLease post your relevant Javascript Editor code.

Kevin

carrarachristophe

Hi Kevin,
Thanks for your prompt reply.
I do confirm.
Here is from table.bibliotheque_oeuvres.js

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.bibliotheque_oeuvres.php',
        table: '#bibliotheque_oeuvres',
        template: '#customForm',
        fields: [
            {
                label: "ISBN:",
                name: "bibliotheque_oeuvres.oeuvre_id"
            },
            {
                label: "Editeur:",
                name: "bibliotheque_oeuvres.editeur",
                type: 'select',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            },
            {
                label: "Collection:",
                name: "bibliotheque_oeuvres.collection",
                type: 'select',
                placeholder: '',
                placeholderDisabled: false,
                placeholderValue: null
            },
        ]
    } );

editor.dependent( 'bibliotheque_oeuvres.editeur', 'php/table.bibliotheque_collections.php' );

But I cannot replace bibliotheque_oeuvres.editeur by bibliotheque_collections.editeur otherwise the field does no show off.
In the example, the js does not mention country.continent but team.continent

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/cascadingLists.php",
        table: "#example",
        fields: [ {
                label: "Name:",
                name:  "team.name"
            }, {
                label: "Continent:",
                name:  "team.continent",
                type:  "select"
            }, {
                label: "Country:",
                name:  "team.country",
                type:  "select"
            }
        ]
    } );

kthorngren

Did you see the blog the example points to?

Looks like the api/countries points to this PHP script which is doing a simple select query to get the options. Maybe that will help.

Kevin

carrarachristophe

Hi Kevin,
I had a look, but I think I am not sure I understand all the data in:

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

• $countries * = table of countries, also containing the field continent, with foreign key?
• country = country field name?
• id = country id in the country table, continent id in the country table?
• name = country name in the country table?
• continent = continent field in country table? continent table name?
• continent = * continent field in country table? continent table name?

kthorngren

I'm not familiar with PHP but the dependent() request is simply fetching a list of options. There is no left join. Use the browser's network inspector tool with the blog example. Open the editor for Cara Stevens and you will see the response is just an array of country options.

Look at the request sent and you will see the parameters sent including:

values[name]: Cara Stevens
values[continent]: 4
values[country]: 168

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

I believe this will build a SQL query something like this:

select id as value, name as label from country where continent = "4"

This should result in an array of countries that match the continent of "4". See the response in the browser's network inspector.

Kevin

carrarachristophe

Hi Kevin,
Thank you for your message.
I finally managed to get it work based on this post.
Here is mycode in case it can help.

<?php

include_once( "lib/DataTables.php" );
 
$bibliotheque_collections= $db
    ->select( 'bibliotheque_collections', ['collection_id as value', 'collection as label'], ['editeur' => $_REQUEST['values']['bibliotheque_oeuvres.editeur']] )
    ->fetchAll();
    
echo json_encode( [
    'options' => [
        'bibliotheque_oeuvres.collection' => $bibliotheque_collections
    ]
] );

carrarachristophe

I am still confused though. The exemple mentions:
* team.name
* team.continent
* team.country
This supposes that the table team contains both country and continent fields.

I was expecting country would be enough, the continent being retreived from the table country. Isn't it possible?

If not, in case we want to use some more sub-divisions, we would have to multiply indefinitely the fields in the table team. Does not really make sense to me.

kthorngren

I was expecting country would be enough, the continent being retreived from the table country. Isn't it possible?

Yes, this is what the blog example has. You can view the source of the page to see the Editor definition.

The example you linked is just shows an example of a more complex data structure.

Kevin

carrarachristophe

Sorry Kevin but I don't know what you mean by "Yes, this is what the blog example has".

Does it mean that I must multiply all the regions in the subregions (for instance continent in country, land',county`, etc?

allan

This supposes that the table team contains both country and continent fields.

You are absolutely correct, there is no need to have both country and continent fields on the host table. You could readily have a LEFT JOIN that would look up the continent id from the country id.

Allan

carrarachristophe

Thank you Alan,
I amended the tables and my code and it apparently works fine for 3 cascading dropdowns (the 1st one filtering the 2nd and the 2nd one filtering the 3rd).
A question though. Any idea why when the page loads, a cascading php file appears twice?

allan

I'd guess that there are two dependent() calls? But they should only trigger an Ajax fetch when the form is shown, not on page load.

If you could give me a link to your page I should be able to track down exactly why this is happening.

Allan

carrarachristophe

Hi Allan,
After investigating, I think I found out the reason why the cascading php was called twice.
This is due to the use of a Select2 field as first field.

1st time it is called:
set
***********************/js/editor.select2.js:267:18
update
***********************/js/editor.select2.js:294:33

2nd time it is called:
update
***********************/js/editor.select2.js:297:24

When I switch to a Selct field, the cascading php is then called once only.